i currently learning database administration and i wanted to ask on what scenario do we need to create a partition?
from what i know we create a bitmap index on column that have low disctinct value(gender,marital status,etc) and there is a query that have 1 or more predicate using those low distinct value column
for example
select * from employees where gender='M' and marital_status ='Married'
and for the partition we create it in order to prevent oracle to do a FTS when our query return most of the row in the table. instead oracle can just read from a partition
for example assuming emp have 1k record(200 M 800 F)
select * from employees where Gender='F';
with this query the optimizer will opting for fts because our query return 80% of the row.and it is good to create a list partition here based on gender. does this method efficient on performace tuning? can anyone tell on what scenario we create a partition?
thanks