Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: FTS in SQL Query ... Advice please?

Re: FTS in SQL Query ... Advice please?

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Tue, 6 Mar 2007 00:59:57 +0100
Message-ID: <17d101c75f82$6285edc0$3c02a8c0@JARAWIN>


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

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 05 2007 - 17:59:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US