Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from smtp-aa.freelists.org (smtp-aa.freelists.org [23.23.80.81]) by malta2546.startdedicated.com (Postfix) with ESMTPS id 85AA910036AB40 for ; Tue, 2 Feb 2021 16:02:33 +0100 (CET) Received: from turing.freelists.org (ip-10-0-0-164.ec2.internal [10.0.0.164]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits)) (No client certificate requested) by smtp-aa.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 894334564D; Tue, 2 Feb 2021 15:02:31 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 8065C3F970; Tue, 2 Feb 2021 15:02:31 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612278151; bh=T5HssxPNWCHABte8AI86d+gKcsdc0rZIhOtcypVTJvg=; h=From:Sender:Sender:From; b=fC0rxx17dmkp324nnqudlFiZwOElC+zkGQ09raHBQz6ahU9//UHA234j+yY+jNMmb P7sd1oq2xIXc/u7wA89zFkBjBv1tKZxsDPmP+c9VCLr8f/S4IF6iUV+GHfvZLcn6id cECZqPg7e2tQy1FdQjgHRfBNNsSd3g/0ZGiLyAyo= X-Virus-Scanned: by FreeLists at turing2.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id v2q1lSM0SUTP; Tue, 2 Feb 2021 15:02:31 +0000 (UTC) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id 523733F972; Tue, 2 Feb 2021 15:02:29 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1612278150; bh=T5HssxPNWCHABte8AI86d+gKcsdc0rZIhOtcypVTJvg=; h=From:Sender:Sender:From; b=oUuKmIPNTf636+dThJwm4j733mTiVh4FMqxHXz3oQTXPO7PGF5W8gfj0tI3I19xcA GoriH3eVxHyomv3JF1icZtKPpfqciLsq7zyB4W4BO/H6Jq7bdJqdvcpzQXVfgXTlj0 iDizQaeVlGSsi6IMzkdUVUIYsiWH+G0rHhgzaobs= Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 02 Feb 2021 15:02:28 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Postfix) with ESMTP id E65873F96E for ; Tue, 2 Feb 2021 15:02:27 +0000 (UTC) Authentication-Results: turing.freelists.org; dkim=pass (2048-bit key; unprotected) header.d=gmail.com header.i=@gmail.com header.a=rsa-sha256 header.s=20161025 header.b=O7/UfPBA; dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id ufg11Fn04Z4t for ; Tue, 2 Feb 2021 15:02:27 +0000 (UTC) Received: from mail-io1-f51.google.com (mail-io1-f51.google.com [209.85.166.51]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (No client certificate requested) by turing.freelists.org (Postfix) with ESMTPS id D4FBE3F85E for ; Tue, 2 Feb 2021 15:02:27 +0000 (UTC) Received: by mail-io1-f51.google.com with SMTP id u20so7484317iot.9 for ; Tue, 02 Feb 2021 07:02:27 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=hD6tyAJpOjbMRdPKd0nG0PkOYFB85ATUlCeUikH8SV0=; b=rdcvNwREvy5/u1p2tIC/3EliLoTGXHc79M7aE5D7etqkXs1Cps1vPQhm3ds9h3q7Xy w0HC00jwSKDxTkthJC30J9KDu1VVHf62vkbLj9DVVBSUNQJvgXNXyeKsqrfJ6oNnSRwC AsWyccN5xKjDnFKX+3C9LoHC74Jb95EoabUSbMO1YGcVUt//fQW2uI02M9pzLK7a/ADh ANb9lUhnvYz77rB6ov7Ue2xCl9AjrirXxo6alQgqCBMkachjSiywtxVvkQobzqjorLkT mN7U9FA0j5CwOYd7qbfSFHUg11+21Hvr3dVLmHLhgd4d1sGOQzWAv4uVlqyIR6jClbrp rUzw== X-Gm-Message-State: AOAM5319gvm/JYuBt1yJkavJfR5RCD+osSzNxdyGCm7Pijl7H5oJE8dy t+1HxOxNP0IuhboddYNnvmkJNrFuy6vjPA8rh7rhBonCnhfs9w== X-Google-Smtp-Source: ABdhPJxi64P/8OQR0CW3QRdZm8irMl9HclA0HhygLdoognHMb7Kf9+boI9eiu7jFCwWDfUReJ0aSaiMFW8rBlQCKsOs= X-Received: by 2002:a02:1dca:: with SMTP id 193mr20963725jaj.39.1612278147260; Tue, 02 Feb 2021 07:02:27 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Lok P Date: Tue, 2 Feb 2021 20:32:15 +0530 Message-ID: Subject: Re: Performance issue on query doing smart scan To: Jonathan Lewis Cc: Oracle L Content-Type: multipart/alternative; boundary="0000000000003f244a05ba5bc286" X-archive-position: 78826 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: loknath.73@gmail.com Precedence: normal Reply-To: loknath.73@gmail.com List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-Subscribe: List-Owner: List-post: List-Archive: X-list: oracle-l --0000000000003f244a05ba5bc286 Content-Type: text/plain; charset="UTF-8" Also Jonathan, apart from having the new index created(which will really need a lot of tests/agreement because this transaction table is 7TB in size and is exposed to heavy batch inserts happening each day 24/7, ~250-300million rows each day) to help cater this query, as you earlier pointing to the double compression issue , should we get this checked with Oracle and we may get some fixes so that even with the current smart scan path the cell node resources can be minimized which will then not impact the flash IO much? *I'd have to check one of the Exadata books (Kerry Osborne et. al.) for your version of Oracle on Exadata, but I think the following stats highlight the symptom:* *cell CUs sent compressed 866,940.00* *cell physical IO bytes sent directly to DB node to balance CPU 15,739,502,952.00* *I'm not sure how Oracle is counting (or possibly double counting) because I would have expected query high CUs to be close to 32KB each so sending 866,940 of them to the database server for decompression should have meant something like 26GB sent to DB node; however there is some consistency visible in other numbers viz:* * EHCC Turbo Scan CUs Decompressed 1,212,123.00 =* * cell CUs sent compressed 866,940.00 + cell CUs sent uncompressed 345,183.00* *Regards* *Lok* On Mon, Feb 1, 2021 at 4:39 PM Lok P wrote: > > Thank You Jonathan. > > I have modified the table names before posting the query. The actual > table/alias name are different. But yes I should have named those better as > it's a bit confusing. > > In regards to the below point for "Between VS >=, <" Operator, I am not > able to fully understand though as input partition key values are bind > values in both the cases , so Oracle has to get the exact value in runtime > from the input binds. I tried executing a sample query on same table by > doing "AND a.part_dt BETWEEN :1 AND :2" and "AND a.part_dt >= :1 > AND a.part_dt<:2" and i am not seeing much difference in elapsed time/CPU > though. Attached is the sql monitor from both the executions. > > *"If you do this then Oracle may be able to discard the predicate (i.e. > not waste CPU testing it) if it can deduce that every row in every relevant > partition is going to satisfy the predicate. This is particularly > interesting for EXADATA with compressed tables, since it MAY mean (and I > can't check it) that Oracle won't have to decompress the column to check > the value, and this MIGHT have a noticeable benefit for some of the > compression options in terms of CPU*." > > In regards to the compression, I am seeing the global stats showing the > distinct values for AANUM as ~173million which may not be fully accurate > considering we are not using incremental stats collection so NDV > calculation might go south, but i tried manually seeing the distinct values > for a few partitions for column AANUM and those are repeated in all of the > partitions. So basically we have AVG ~250million rows in each partition and > in those ~135million distinct aanum values , which comes around ~2 index > entries for each AANUM and in that prospect the compression of the local > index (AANUM,TXID) won't be helpful. And also the same set of AANUM are > repeated in each of the partitions. Attached is the columns stats for 4 > sample partitions. > > And as I see from sample four partitions, out of the columns used as > filters in the query the column O_ID seems quite unique , but the way it's > used in the query(as a case statement in the right hand side) is not > filtering much rows. And as you rightly said, the column d_ind is having > two distinct values 'Y' and 'N' but it's having ~40% and 60% rows > respectively distributed. So the index on this column is not going to help > much. I am planning to first try an index on AANUM,DID and see the > performance. Else possibly last option is to have a big index comprising > all the columns ( aanum, did, d_ind, o_id, pnm. ) to avoid table read > fully, but again considering this table is a big transactional table we may > have to think twice before taking decision on creating new indexes. > > Regards > > Lok > > On Fri, Jan 29, 2021 at 4:21 PM Jonathan Lewis > wrote: > >> *Actually the partition column I_DT is on TAB2 which is part of another >> with clause query but full scanning that table is not the problem here as >> we see from the sql monitor. It's the scanning of TAB1 which is partitioned >> on PART_DT is causing the issue. And we do have an explicit filter on this >> column for partition pruning, but anyway our requirement is to scan across >> all the ~180 partitions only.* >> >> Given that you have a table TAB1, which you've included in a CTE that >> you've aliased TAB1, which then gets an alias of "a" in the main query; >> with an equally bad appalli1ng n1aming convention involving TAB2 it's not >> entirely surprising that I managed to cross over the two names when working >> through your problem. Do you really have such bad coding standards? >> >> The point remains, however you said that you scanned all the partitions, >> and that the partitioning column was part_dt, and the SQL you sent us >> originally has the predicate "AND a.part_dt BETWEEN :1 AND :2". >> BETWEEN is a very bad option for a predicate on a partition key.A >> partition contains data that is strictly less than the upper boundary, and >> greater than or equal to the upper bound of the previous partitions, so the >> ideally range-based predicate should be of the form: >> part_dt >= {date1} and part_dt < {date2} >> If you do this than Oracle may be able to discard the predicate (i.e. not >> waste CPU testing it) if it can deduce that every row in every relevant >> partition is going to satisfy the predicate. This is particularly >> interesting for EXADATA with compressed tables, since it MAY mean (and I >> can't check it) that Oracle won't have to decompress the column to check >> the value, and this MIGHT have a noticeable benefit for some of the >> compression options in terms of CPU. >> >> >> Index compression and table compression have nothing to do with each >> other. Also the "much longer running" of the query when forced through >> (AANUM, TXID) isn't about the large number of distinct values of TXID, it's >> about the relatively small number of distinct values of AANUM or, to be >> more accurate, the large number of rows for each value of AANUM that are >> now being acquired by single block access. >> >> Your thoughts on compression do make sense as a general strategy for >> reducing storage space and increasing the probability of caching index leaf >> blocks. Since you have 173 million distinct value for AANUM in a table with >> 56 Billion rows then on average each value of AANUM has about 330 related >> rows and if this table were not partitioned you would automatically say >> that this is a good index for compression. BUT this is a local index on a >> table with 180 partitions, so each partition MIGHT only have one or two >> index entries for each value of AANUM in which case you probably wouldn't >> want to compress it; on the other hand perhaps the AANUM has values which >> correlate with time, so maybe any one value of AANUM appears in only about >> 10 partitions, in which case each partition would average 33 rows per AANUM >> and the index would be worth compressing. >> >> Regarding index columns: >> >> >> >> * WHERE a.aanum = stage.anum AND a.did = stage.did >> AND a.d_ind = 'Y' AND a.o_id LIKE CASE WHEN >> stage.al = 'XXXX' THEN stage.o_id ELSE a.o_id END >> AND a.part_dt BETWEEN :1 AND :2* >> >> >> >> >> >> >> * WHERE TAB1.anum(+) = stage.anum AND TAB1.did(+) = >> stage.did AND TAB2.anum(+) = stage.anum AND TAB2.did(+) = >> stage.did AND TAB1.pnm(+) = stage.pnm AND TAB2.pnm(+) = >> stage.pnm* >> >> I made two mistakes with my advice on (anum, did). >> >> First, I forgot that the final where clause was against CTEs, so I missed >> the need to have d_ind and o_id in the index if you wanted to avoid >> visiting the table unnecessarily; secondly I missed the TAB1.pnm predicate >> that was in the middle of your TAB2 predicates in the final where clause - >> you'd need to have this in the index as well to avoid visiting the table. >> Again there's a detail to check in your coding standards - the three TAB1 >> predicates should have been together to avoid the risk of that mistake >> happening. (Just to add a positive note, I approve of the "next_table = >> current_table" arrangement of the predicates, some people would have >> written "stage.anum = TAB1.anum(+)") >> >> Two more thought on indexing - a predicate like d_ind='Y' suggests the >> column is a yes/no column. >> a) If almost all the data has d_ind='Y' then there may be no significant >> benefit in having that column in the index. If only a tiny fraction of >> the data has d_ind='Y' then if you can modify the query, you might be able >> to create a very small function-based index that would make this query much >> more efficient while adding very little to the workload. >> b) The list of relevant columns for the index has gone up to 5 for the >> "perfect" index - in no particular order: aanum, did, d_ind, o_id, pnm. >> If you know your data you may be able to decide that a combination of 4 or >> 3 of these columns is "good enough" to reduce the workload dramatically at >> query time while not adding too great a maintenance overhead. >> >> Finally a generic warning: >> *a.o_id LIKE CASE WHEN stage.al = 'XXXX' THEN >> stage.o_id ELSE a.o_id END * >> >> This is a variant of a far commoner construct which is erroneously >> written as the model for "give me everything unless the user requests >> specific rows", viz: >> *where colX = nvl(:bind_variable, colX)* >> (See: https://jonathanlewis.wordpress.com/2007/01/09/conditional-sql/ ) >> >> The "LIKE" is sufficiently different that it's possible the predicate is >> doing exactly what it's supposed to do, but it's worth mentioning that if >> o_id is NULL then the predicate evaluates to false (even when stage.al = >> 'XXXX') and this may not be the intention. >> >> >> Regards >> Jonathan Lewis >> >> >> >> --0000000000003f244a05ba5bc286 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Also Jonathan, apart from having the new index created(whi= ch will really need a lot=C2=A0of tests/agreement because this transaction = table is 7TB in size and is exposed to heavy batch inserts happening each d= ay 24/7, ~250-300million rows each day) to help cater this query, as you ea= rlier=C2=A0pointing to the double compression issue , should we get this ch= ecked with Oracle and we may get some fixes so that even with the current s= mart scan path the cell node resources can be minimized which will then not= impact the flash IO much?

I'd have to check= one of the Exadata books (Kerry Osborne et. al.) for your version of Oracl= e on Exadata, but I think the following stats highlight the symptom:

cell CUs sent compressed=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 866,940.00
cell physical IO bytes sent directly to DB node to balance CPU= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 15,739,502,952.00
=
I'm not sure how Oracle is counting (or possib= ly double counting) because I would have expected query high CUs to be clos= e to 32KB each so sending 866,940 of them to the database server for decomp= ression should have meant something like 26GB sent to DB node; however ther= e is some consistency visible in other numbers viz:

=C2=A0 EHCC Turbo Scan CUs Decompressed 1,212,123.00 =3D
=C2=A0 cell CUs sent compressed 866,940.00 + cell CUs se= nt uncompressed 345,183.00

Reg= ards
Lok

On Mon, Feb 1, 2021 at 4:39 PM Lok P <= ;loknath.73@gmail.com> wrote= :


Thank You Jonathan.

I have modified the table na= mes before posting the query. The actual table/alias name are different. Bu= t yes I should have named those better as it's a bit confusing.

=

In regards to the below point= for "Between VS >=3D, <" Operator, I am not able to fully = understand though as input partition key values are bind values in both the= cases , so Oracle has to get the exact value in runtime from the input bin= ds. I tried executing a sample query on same table by doing "AND a.par= t_dt BETWEEN :1 AND :2" and "AND a.part_dt >=3D :1 AND=C2=A0a.= part_dt<:2" and i am not seeing much difference in elapsed time/CPU= though. Attached is the sql monitor from both the executions.

"If you do this then Oracl= e may be able to discard the predicate (i.e. not waste CPU testing it) if i= t can deduce that every row in every relevant partition is going to satisfy= the predicate. This is particularly interesting for EXADATA with compresse= d tables, since it MAY mean (and I can't check it) that Oracle won'= t have to decompress the column to check the value, and this MIGHT have a n= oticeable benefit for some of the compression options in terms of CPU.&= quot;

In regards to th= e compression, I am seeing the global stats showing the distinct values for= AANUM as ~173million which may not be fully accurate considering we are no= t using incremental stats collection so NDV calculation might go south, but= i tried manually seeing the distinct values for a few partitions for colum= n AANUM and those are repeated in all of the partitions. So basically we ha= ve AVG ~250million rows in each partition and in those ~135million distinct= aanum values , which comes around ~2 index entries for each AANUM and in t= hat prospect the compression of the local index (AANUM,TXID) won't be h= elpful. And also the same set of AANUM are repeated in each of the partitio= ns. Attached is the columns stats for 4 sample partitions.

And as I see from sample four partiti= ons, out of the columns used as filters in the query the column O_ID seems = quite unique , but the way it's used in the query(as a case statement i= n the right hand side) is not filtering much rows. And as you rightly said,= the column d_ind is having two distinct values 'Y' and 'N'= but it's having ~40% and 60% rows respectively distributed. So the ind= ex on this column is not going to help much.=C2=A0I am planning to first tr= y an index on AANUM,DID and see the performance. Else possibly last option = is to have a big index comprising all the columns ( aanum, did, d_ind, o_id= , pnm. ) to avoid table read fully, but again considering this table is a b= ig transactional table we may have to think twice before taking decision on= creating new indexes.

Reg= ards

Lok


=
On Fri, Ja= n 29, 2021 at 4:21 PM Jonathan Lewis <jlewisoracle@gmail.com> wrote:
Actually the partition column I_DT is on TAB2 which is part of anot= her with clause query but full scanning that table is not the problem here = as we see from the sql monitor. It's the scanning of TAB1 which is part= itioned on PART_DT is causing the issue.=C2=A0 And we do have an explicit f= ilter on this column for partition=C2=A0pruning, but anyway our requirement= is to scan across all the ~180 partitions=C2=A0only.

Given that you have a table TAB1,= which you've included in a CTE that you've aliased TAB1, which the= n gets an alias of "a" in the main query; with an equally bad app= alli1ng n1aming convention involving TAB2 it's not entirely surprising = that I managed to cross over the two names when working through your proble= m. Do you really have such bad coding standards?

T= he point remains, however you said that you scanned all the partitions, and= that the partitioning column was part_dt, and the SQL you sent us original= ly has the predicate "AND a.part_dt BETWEEN :1 AND :2".=C2=A0=C2= =A0=C2=A0
BETWEEN is a very bad option for a predicate on a = partition key.A partition contains data that is strictly less than the uppe= r boundary, and greater than or equal to the upper bound of the previous pa= rtitions, so the ideally range-based predicate should be of the form:
=
=C2=A0=C2=A0 part_dt >=3D {date1} and part_dt < {date2}
If you do this than Oracle may be able to discard the predicate (i.e. not= waste CPU testing it) if it can deduce that every row in every relevant pa= rtition is going to satisfy the predicate. This is particularly interesting= for EXADATA with compressed tables, since it MAY mean (and I can't che= ck it) that Oracle won't have to decompress the column to check the val= ue, and this MIGHT have a noticeable benefit for some of the compression op= tions in terms of CPU.


Index c= ompression and table compression have nothing to do with each other.=C2=A0 = Also the "much longer running" of the query when forced through (= AANUM, TXID) isn't about the large number of distinct values of TXID, i= t's about the relatively small number of distinct values of AANUM or, t= o be more accurate, the large number of rows for each value of AANUM that a= re now being acquired by single block access.

Your thoughts on compression do make sense as a general strategy for reduc= ing storage space and increasing the probability of caching index leaf bloc= ks. Since you have 173 million distinct value for AANUM in a table with 56 = Billion rows then on average each value of AANUM has about 330 related rows= and if this table were not partitioned you would automatically say that th= is is a good index for compression. BUT this is a local index on a table wi= th 180 partitions, so each partition MIGHT only have one or two index entri= es for each value of AANUM in which case you probably wouldn't want to = compress it; on the other hand perhaps the AANUM has values which correlate= with time, so maybe any one value of AANUM appears in only about 10 partit= ions, in which case each partition would average 33 rows per AANUM and the = index would be worth compressing.

Regarding in= dex columns:

=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0=C2=A0 WHERE =C2=A0 =C2=A0 a.aanum =3D stage.anum =C2= =A0 =C2=A0AND a.did =3D stage.did =C2=A0 =C2=A0 =C2=A0AND a.d_ind =3D '= Y'
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0AND a.o_id LIKE =C2=A0 =C2=A0 CASE =C2=A0 WHEN stage.al =3D 'XXXX' THEN stage.o= _id =C2=A0ELSE a.o_id =C2=A0 =C2=A0END
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0AND a.part_dt BETWEEN :1 AND := 2

=C2=A0=C2=A0 WHERE =C2=A0 =C2=A0 TAB1.anu= m(+) =3D stage.anum
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0AND TAB1.did(+) = =3D stage.did
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0AND TAB2.anum(+) =3D sta= ge.anum
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0AND TAB2.did(+) =3D stage.did<= br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0AND TAB1.pnm(+) =3D stage.pnm
=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0AND TAB2.pnm(+) =3D stage.pnm
=
I made two mistakes with my advice on (anum, did).

First, I forgot that the final where clause was against CTE= s, so I missed the need to have d_ind and o_id in the index if you wanted t= o avoid visiting the table unnecessarily; secondly I missed the TAB1.pnm pr= edicate that was in the middle of your TAB2 predicates in the final where c= lause - you'd need to have this in the index as well to avoid visiting = the table. Again there's a detail to check in your coding standards - t= he three TAB1 predicates should have been together to avoid the risk of tha= t mistake happening.=C2=A0 (Just to add a positive note, I approve of the &= quot;next_table =3D current_table" arrangement of the predicates, some= people would have written=20 "stage.anum =3D TAB1.anum(+)")=C2=A0

Two more thought on indexing - a predicate like d_ind=3D'Y' sugge= sts the column is a yes/no column.
a)=C2=A0 If almost all the dat= a has d_ind=3D'Y' then there may be no significant benefit in havin= g that column in the index.=C2=A0=C2=A0 If only a tiny fraction of the data= has d_ind=3D'Y' then if you can modify the query, you might be abl= e to create a very small function-based index that would make this query mu= ch more efficient while adding very little to the workload.
b) Th= e list of relevant columns for the index has gone up to 5 for the "per= fect" index - in no particular order: =C2=A0 aanum, did, d_ind, o_id, = pnm.=C2=A0 If you know your data you may be able to decide that a combinati= on of 4 or 3 of these columns is "good enough" to reduce the work= load dramatically at query time while not adding too great a maintenance ov= erhead.

Finally a generic warning:
a.o_id LIKE =C2=A0 =C2=A0 CASE =C2=A0 WHEN stage.al =3D 'XXXX' THEN stage.o_id =C2=A0ELS= E a.o_id =C2=A0 =C2=A0END

This is a variant of a far commoner construct= which is erroneously written as the model for "give me everything unl= ess the user requests specific rows", viz:
where col= X=C2=A0 =3D nvl(:bind_variable, colX)
(See: https://jonathanlewis.wordpress.com/2007/01/09/conditional-sql/ )

The "LIKE" is sufficiently different that i= t's possible the predicate is doing exactly what it's supposed to d= o, but it's worth mentioning that if o_id is NULL then the predicate ev= aluates to false (even when s= tage.al =3D 'XXXX') and this may not be the intention.


Regards
Jonathan Lewis
=


--0000000000003f244a05ba5bc286-- -- http://www.freelists.org/webpage/oracle-l