Re: FTS in SQL Query ... Advice please?
- From: "jaromir nemec" <jaromir@xxxxxxxxxxxx>
- To: <VIVEK_SHARMA@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 6 Mar 2007 00:59:57 +0100
Hello Vivek,
> AND GAM.del_flg != :"SYS_B_1"
As already mentioned I wouldn't use cursor sharing for such kind of queries. In
most cases I assume you wouldn't prefer to use the same plan to access the
deleted and the not deleted records.
> Qs What Options exist to avoid FTS on GAM in the below SQL Query?
The partition *is* the best index of the fact tables in DW, so I'd try to
leverage partition pruning.
Is there a way e.g. to infer SOL_ID and/or ACID out of the predicates in the
query? ACID sound somehow as a surrogate of the account_num / account_prefix.
If this is not possible (and your query is a typical one) you may ask if your
partitioning schema is optimal.
If sol_id and acid are somehow correlated (I assume EAB is a detail table of
GAM), it's a pity Oracle doesn't know about that as it closes the door for the
partition wise join.
First after partition pruning I'd check if index access of selected
partition(s) is better than full (partition) scan.
The positive side effect of the pruning of both tables is that the optimizer
can freely choose the join method; in most cases (except for very restrictive
index access) a hash join will be opened. (the NL is probably the bottleneck in
your query - not the FTS)
HTH
Jaromir D.B. Nemec
Other related posts:
