Oracle SQL: Left Joins will not use Indexes

Hi vocogov,

Are the referenced columns nullable?  Even though the columns may not contain NULL values, the optimizer may be detecting that the row data could contains nulls and would need to bypass the index to guarantee an accurate join.

If you're in a test environment, try altering the column to NOT NULL.  You may need to analyze the table again to update the statistics.


Kent

Thanks Kent.  :)

The columns TAXYR and PARID  are "not null" columns for all the tables.   The CUR column is not set to "Not Null".    I'll have to get permission from the Developers to change the CUR column to a "not null" in the test environment.  

Do you think that with two of the columns being "not null" that it matters on this 3rd column?

Offhand, I wouldn't think so but Oracle might have a different opinion.

Can you create your own table declaring NOT NULL where needed, populate it, and run your query against it?

Also, if you're sure those column are fully populated you can just use an INNER JOIN.  The result will be identical to the OUTER JOIN.

I'm sure the CUR column is populated for all records.  

Good idea to create a copy of the table.  I will start on that.

For that last comment, I'm not a developer and have a hard time understanding the developer's code.  Can you give me an idea of how to recode it for a INNER join?

Change the word 'LEFT' to 'INNER'.  :)

Can you post the plan. Have you gathered stats?

I've copied one of the tables (OWNDAT) along with some indexes on the new table.  
Have changed the view to reference the new table and changed the LEFT's to INNER.
Ran stats on the new table.  

Will try the new view now.

You need to be aware of the full implication of changing a LEFT JOIN to inner join

If your tables_A has the following records
ID
100
200
300
400

and table_B has the following records
ID
100
200

table_A LEFT OUTER JOIN table_B will return
table_A.id        table_B.id
100                   100
200                   200
300
400

table_A INNER JOIN table_B will return
table_A.id        table_B.id
100                   100
200                   200

Am attaching a live plan for the view (plan1).  I'm doing a 'select * from view';    

Also attaching a the output from "autotrace traceonly' (plan2).  It's a little bit easier to read.
select-all-from-view-plan1.txt
select-all-from-view-plan2.txt

I have to leave work for the day.  Hopefully you guys will be around tomorrow.  Thank you very much.

>>Here is a snippet of the view.  

Snippets don't help if we cannot see the access of the tables causing the problems.

I would start by looking at what is joining to LEGDAT  and by what columns.

Yes, as mentioned above, current stats can make all the difference!

Thanks everyone.    The "snippet" of the view, when executed, has the same behavior as the full view.  I just took out a bunch of references to other table and columns to make it easier to read.  

I'm uploading the full version of the main view for you called "appraisal_view".   I put back to be "LEFT" joins.

The table LEGDAT is referred to by the view "LEGDAT_VIEW",  which is called by the main query.    I am uploading the "LEGDATA_VIEW" as well.

One last thing...I'm deleting the explain plans I uploaded and will upload fresh ones.
appraisal_view
legdat_view

You still haven't confirmed statistics are up to date.

Make sure there are indexes on L.PARID, L.TAXYR AND L.CUR.  Granted, even if there is there is no guarantee they will be used but I would start trying to tune the query against LEGDAT.

Here are two versions of the explain.    

Moving forward to run stats on all the tables involved.  

Slightvw:  For the indexes, please specify details.   How many indexes and on which columns?  If multiple columns, is there a particular order you'd like to start with?
Explain_Plan_1
Explain_Plan_2

FULL scan itself isn't bad.

You seem to be reading a lot data from your tables. Can you try a modified query as below.
Please verify the HASH order in the plan remains unchanged.

with legdata as (
    select  /*+ NO_MERGE */ *
    from    IAS.LEGDATL_VIEW lv
    where   lv.TAXYR    = (SELECT THISYEAR FROM IAS.AASYSJUR)
), aprdata as (
    select  /*+ NO_MERGE */ *
    from    IAS.APRVAL ad
    where   ad.TAXYR    = (SELECT THISYEAR FROM IAS.AASYSJUR)
)
select 
L.ADRDIR ADDRESS, 
L.TAXDIST DISTRIBUTION, 
L.UNITNO SUITE_NUMBER, 
L.USER10 DATE_CREATED, 
O.OWN1 OWNER1, 
O.OWN2 OWNER2, 
NVL(A2.VAL05.0) AS NEW_CONSTRUCTION,
NVL(A.APRBLDG,0) AS BLDG_VALUE
FROM      IAS.PARDAT P
            LEFT JOIN legdata L
                  ON      P.PARID = L.PARID AND P.CUR = L.CUR
            LEFT JOIN aprdata A
                  ON P.PARID = A.PARID AND P.CUR = A.CUR
            LEFT JOIN IAS.OWNDAT O
                  ON P.PARID = O.PARID AND P.TAXYR = O.TAXYR      AND P.CUR = O.CUR
            LEFT JOIN IAS.ASMT A2
                   ON P.PARID = A2.PARID AND P.TAXYR = A2.TAXYR AND P.CUR = A2.CUR
WHERE      P.TAXYR = (SELECT THISYEAR FROM IAS.AASYSJUR)

Select allOpen in new window

Thanks Sujith.  :)
The code will not compile.  It's giving the following error:

Failed to commit: ORA-00909: invalid number of arguments

any particular reason why you do don't join the year like this :

FROM	USR.PARDAT P
  inner join USR.AASYSJUR on P.TAXYR =  AASYSJUR.THISYEAR 
		LEFT JOIN USR.LEGDAT_VIEW L
... 

Select allOpen in new window

>> The code will not compile

which code. You need to sort out your compilation issues etc. What I posted is a suggestion to move the filtering out of the HASH join. You may read the concept and change your code/view accordingly.

Sujith...I see.    I'm not a developer and didn't write the code.  I'm the "DBA" who is trying to make the database work with their code.  

When I look at this code, my eyes cross and I long for retirement.  

If you want me to rewrite something, you'll have to spell it out for me at the novice level.

>> How many indexes and on which columns?

I don't know your data and I don't know what values that query will be going after.  Index or no index depends on many factors.  Cardinality probably being the biggest.

People make entire careers out of nothing but tuning.  People have written entire books on how to do it.

Sorry, but there isn't any magic that we can look at a query and point to an exact "fix".

I looked at the original plan and the LEGDAT access jumped out at me.  I would probably take the larger query and remove everything except that join and tune that.

select 
L.ADRDIR ADDRESS, 
L.TAXDIST DISTRIBUTION, 
L.UNITNO SUITE_NUMBER, 
L.USER10 DATE_CREATED
FROM      IAS.PARDAT P
            LEFT JOIN IAS.LEGDATL_VIEW L
                  ON      P.PARID = L.PARID AND P.TAXYR = L.TAXYR AND P.CUR = L.CUR
WHERE      P.TAXYR = (SELECT THISYEAR FROM IAS.AASYSJUR)

Select allOpen in new window

>> People make entire careers out of nothing but tuning.  People have written entire books on how to do it.
Hehe, yeah, he just left ... :)

I like slightwv's suggestion to start with just one join and try to get that to be efficient.  Then, add another table and make sure that one is efficient, etc. until you have all the joins working.

I never like seeing left joins because they can prevent Oracle from using indexes.  Are you sure that you need these to be left joins?  If you aren't sure, test them as both left joins and inner joins and see if the number of rows returned is the same or different.  Left joins are only required if one of the tables is missing values that exist in the other table.  We don't know your data or your application, so we don't know if this is likely or possible, or not.

Thanks slightvw.  That is what I did this week.  I saw the LEGDAT was the more costly one so I focused on that for 2 days by thinning it down like you suggest.  
 
Those index combinations I mentioned at the beginning, I also did on the LEGDAT table but with zero success.  I contemplated adding a rule to force the use of an index but decided to open the question on EE instead.  

I am willing to focus on the LEGDAT.  Surely if we can get it running proficiently with just that, then it's likely to work on the other tables as well.  

What next step do you think to do if we focused on LEGDAT?

BTW...the stats have completed on the tables.   Ready to plow forward.

when you want to find out what's slow,

comment a piece of the query and try it
keep commenting pieces and sooner or later you'll get to a point where it's lightning fast (or at a minimal time)

then uncomment the piece which you found to be slow
and verify it's only that piece

sample comment piece 1:

select 
L.ADRDIR ADDRESS, 
L.TAXDIST DISTRIBUTION, 
L.UNITNO SUITE_NUMBER, 
L.USER10 DATE_CREATED, 
O.OWN1 OWNER1, 
O.OWN2 OWNER2, 
NVL(A2.VAL05.0) AS NEW_CONSTRUCTION
/* piece 1 ,NVL(A.APRBLDG,0) AS BLDG_VALUE */
FROM      IAS.PARDAT P
            LEFT JOIN IAS.LEGDATL_VIEW L
                  ON      P.PARID = L.PARID AND P.TAXYR = L.TAXYR AND P.CUR = L.CUR
/* piece 1 
--            LEFT JOIN IAS.APRVAL A
--                   ON P.PARID = A.PARID AND P.TAXYR = A.TAXYR AND P.CUR = A.CUR
*/
            LEFT JOIN IAS.OWNDAT O
                  ON P.PARID = O.PARID AND P.TAXYR = O.TAXYR      AND P.CUR = O.CUR
            LEFT JOIN IAS.ASMT A2
                   ON P.PARID = A2.PARID AND P.TAXYR = A2.TAXYR AND P.CUR = A2.CUR
WHERE      P.TAXYR = (SELECT THISYEAR FROM IAS.AASYSJUR)

Select allOpen in new window

sample comment piece 1 and 2:

select 
L.ADRDIR ADDRESS, 
L.TAXDIST DISTRIBUTION, 
L.UNITNO SUITE_NUMBER, 
L.USER10 DATE_CREATED, 
O.OWN1 OWNER1, 
O.OWN2 OWNER2 
/* piece 2 , NVL(A2.VAL05.0) AS NEW_CONSTRUCTION */
/* piece 1 ,NVL(A.APRBLDG,0) AS BLDG_VALUE */
FROM      IAS.PARDAT P
            LEFT JOIN IAS.LEGDATL_VIEW L
                  ON      P.PARID = L.PARID AND P.TAXYR = L.TAXYR AND P.CUR = L.CUR
/* piece 1 
--            LEFT JOIN IAS.APRVAL A
--                   ON P.PARID = A.PARID AND P.TAXYR = A.TAXYR AND P.CUR = A.CUR
*/
            LEFT JOIN IAS.OWNDAT O
                  ON P.PARID = O.PARID AND P.TAXYR = O.TAXYR      AND P.CUR = O.CUR
/* piece 2
--            LEFT JOIN IAS.ASMT A2
--                   ON P.PARID = A2.PARID AND P.TAXYR = A2.TAXYR AND P.CUR = A2.CUR
*/
WHERE      P.TAXYR = (SELECT THISYEAR FROM IAS.AASYSJUR)

Select allOpen in new window

that's just one way of finding where to start looking

Mark Geerlings:  That's a good explanation of the joins.  When you say "Left joins are only required if one of the tables is missing values that exist in the other table. ",  by "missing", do you mean null values?  or no matching character?

Geert G:   I agree completely.  Slightvw said the same thing.   So I would love to just focus on the LEGDAT table.   I've taken Slightvw's suggested and made a small view that accesses only the LEGDAT TABLE.  Attached is the explain plan.  Belos is a list of the  current indexes that make references to the columns being called by the view.
LEGDAT_Explain_Plan_1.txt

LEGDAT table:

Columns:
  JUR  (NOT NULL)
  PARID  (NOT NULL)
  TAXYR  (NOT NULL)
  SEQ  (NOT NULL)
 * All other columns are nullable.

 
Indexes related to the columns in the view:

LEGDAT_B:            BLOCK, JUR, TAXYR
LEGDAT_JURPIN:  "JUR"||':'||"PARID", PARID, JUR, TAXYR, CUR
LEGDAT_L:             LOT, JUR, TAXYR
LEGDAT_L1:           LEGAL1, TAXYR, JUR, PARID
LEGDAT_Q:            QUALIFIER, JUR, TAXYR
LEGDAT_S:             TAXYR, JUR, SUBDNUM
LEGDAT_SP:           SPLITNO, TAXYR, JUR
LEGDAT_U:             PARID, TAXYR, JUR

>>and made a small view

Why?  creating the view isn't necessary and is an extra step.

>>do you mean null values?  or no matching character?

Set up a small test case and look at how joins work:

drop table tab1 purge;
create table tab1(col1 char(1));

insert into tab1 values('a');
insert into tab1 values('b');
insert into tab1 values('c');
insert into tab1 values('d');
commit;

drop table tab2 purge;
create table tab2(col1 char(1));

insert into tab2 values('a');
insert into tab2 values('b');
commit;

select a.col1, b.col1
from tab1 a left join tab2 b on a.col1=b.col1;

select a.col1, b.col1
from tab1 a join tab2 b on a.col1=b.col1;

Select allOpen in new window

Output form the two queries:

C C
- -
a a
b b
d
c


C C
- -
a a
b b

Select allOpen in new window

By "missing", I mean no matching character, as in the example that Sujith posted yesterday with values from table_A and table_B.

That's helpful Slightvw.    When doing the regular join (instead of  left join) I get fewer records in the result set.

In looking at the original view, I see selected columns with "NVL..." specified.  I'm assuming that is to fill in a value if a column is null in the result set.  If that's the case, then it make sense the developer wants the LEFT join.

I created 6 indexes and none of them were used by the select statement:

select
L.ADRDIR ADDRESS,
L.TAXDIST DISTRIBUTION,
L.UNITNO SUITE_NUMBER,
L.USER10 DATE_CREATED
FROM      IAS.PARDAT P
            LEFT JOIN IAS.LEGDATL L
                  ON      P.PARID = L.PARID AND P.TAXYR = L.TAXYR AND P.CUR = L.CUR
WHERE      P.TAXYR = (SELECT THISYEAR FROM IAS.AASYSJUR);

CREATE INDEX IAS.LEGDAT_KURPA_IDX2 on IAS.LEGDAT (PARID, TAXYR, CUR);
CREATE INDEX IAS.LEGDAT_KURPA_IDX2 ON IAS.LEGDAT (CUR, TAXYR, PARID);
CREATE INDEX IAS.LEGDAT_KURPA_IDX3 ON IAS.LEGDAT (PARID);
CREATE INDEX IAS.LEGDAT_KURPA_IDX4 ON IAS.LEGDAT (TAXYR);
CREATE INDEX IAS.LEGDAT_KURPA_IDX5 ON IAS.LEGDAT (TAXYR, PARID);
CREATE INDEX IAS.LEGDAT_KURPA_IDX6 ON IAS.LEGDAT (PARID,TAXYR);

Just for grins, what happens to the plan if you remove the LEFT on the join?

select 
L.ADRDIR ADDRESS, 
L.TAXDIST DISTRIBUTION, 
L.UNITNO SUITE_NUMBER, 
L.USER10 DATE_CREATED
FROM      IAS.PARDAT P
            JOIN IAS.LEGDATL L
                  ON      P.PARID = L.PARID AND P.TAXYR = L.TAXYR AND P.CUR = L.CUR
WHERE      P.TAXYR = (SELECT THISYEAR FROM IAS.AASYSJUR);

Select allOpen in new window

For normal (inner) joins, I expect this index would be the only one you need to help the join to the LEGDAT table:
CREATE INDEX IAS.LEGDAT_KURPA_IDX2 on IAS.LEGDAT (PARID, TAXYR, CUR);

But, if a left join here returns more rows, and you want them to be included, it gets more complicated to determine which index(es) if any, may be beneficial.

Slightvw:  The cost was exactly the same.  The difference is I got fewer records when I removed the "LEFT" on the join.

I am thinking that Sujith's observation that I'm pulling in too many records may be the reason that no indexes are being used.  There's just too many records meeting the criteria for each of those joins.  I haven't calculated it but I'd swag it at 90% of the table is the result set for each join.

If the actual number of rows you need from an Oracle table is over 15%, you usually don't benefit from an index, unless the records in the table happen to be physically in order by the value(s) in the index (which is unusual).  That percentage is likely smaller than you may have expected.

For materialized views:   Isn't that a static version of the data that the users can select from?   This materialized view being "refreshed" periodically so they get current data?  

 If an index isn't used by the regular view, how could one make indexes for the materialized view?  

Wouldn't it be the same as if I just created a table based on the view?  "create table xxxx as select * from view;" ?

Thanks so much for your help on this.

Late yesterday afternoon, I created a table from the view.   Column ALTKEY is a not-null column of distinct values....no duplicates.

So with this new table ( 342,461 records), I created an index on ALTKEY.

Did statistics at 100% for both table and index.

When I select from the table where ALTKEY=xxxxx, I see that it does not use the index and the cost is 4K.    The query is quick though.  

Does this seem right to you?

"For materialized views:   Isn't that a static version of the data that the users can select from? "
Yes
 
"This materialized view being "refreshed" periodically so they get current data?"
Yes"

"how could one make indexes for the materialized view?"
Easily, just like on any table that you create.

"Wouldn't it be the same as if I just created a table based on the view?"
Yes, but a materialized view can be refreshed either automatically or on-demand.

"Did statistics at 100% for both table and index."
You rarely need that.  Usually, statistics with "AUTO_SAMPLE_SIZE" are good enough (and are *MUCH* faster to calculate.)   In some rare cases, statistics at 100% can be better.

"When I select from the table where ALTKEY=xxxxx, I see that it does not use the index ..."
How many distinct values does this column have?  How many, or what percentage of the records have this value: ALTKEY=xxxxx?

All 342,461 rows in the table have a unique ALTKEY value.  

I could set up a primary key on this column if I wanted but I just created an index instead.

>> If an index isn't used by the regular view, how could one make indexes for the materialized view?  

I'm suggesting creating a Materialize View on the entire select with all the joins.  Then you can create any index the MV for the final query you need.

>>Wouldn't it be the same as if I just created a table based on the view?

What Marack said:  A CATS is static.  A MV is refreshed as data in the base tables change.

Thanks.  But either way, with this simple table of 342,461  rows, with a column of unique data, one would think it'd use the index.  Why oh why won't it use the index?

>>Why oh why won't it use the index?

That was explained above.  If Oracle needs the 90% of the data from the table you claim, why waste time reading index blocks into memory then reading 90% of the table blocks?  You would end up reading more blocks overall, thus, more I/O, thus more time.

Either I'm not being clear or I'm being stupid (please don't pick the last one)....

To simplify things, I've  created a table from the view.  At this point I don't care about refreshing the data.  Just getting a query against the table running efficiently.  

For the table I created, there are 342,461  rows.    The column "altkey" contains unique values.  

I'm searching for one of those unique values but it is not using the index.  There is only one row that it finds of the 342K records which is equal to xxxxx (I know this absolutely).  

Here's what I did to create the table and index.   Also is the select statement and the explain plan.  The query runs quickly but the cost is 4K.  No index is used. This doesn't make sense to me.  

create table static_table as select * from appraiser_view;

create index static_table_idx on static_table (altkey);

select * from static_table where altkey=xxxxx;

Elapsed: 00:00:00.15

Execution Plan
----------------------------------------------------------
Plan hash value: 1390295238
------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Rows  | Bytes | Cost (%CPU)| Time  |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                            |     1 |   299 |  4019   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| STATIC_TABLE |     1 |   299 |  4019   (1)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("ALTKEY")=XXXXXXX)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      14662  consistent gets
          0  physical reads
          0  redo size
       3168  bytes sent via SQL*Net to client
        373  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Off the top of my head, I cannot explain that.  I do see that the table is all in memory (all consistent gets).  Assuming an 8K block size the table is about 122 Meg.

I would have bet money an index would have been used.

For grins, does it do a range scan with:
select altkey from static_table where altkey=xxxxx;

This so weird and interesting!!

select * from static_table where altkey=xxxxx;   <-- does NOT use index

select * from static_table where altkey='xxxxx';   <--- DOES use the index!

select altkey from static_table where altkey=xxxxx;   <-- DOES use the index!

select altkey from static_table where altkey='xxxxx';  <-- DOES use the index!

Wow!  This is very helpful!   Now I can have the developer who is trying to extract data from this table (that took 8 hours before he had to cancel it ...not the view...just the table) to make sure there are single quotes around the 'xxxxx'.  

Oh boy I can't wait to have him try it.  But I have to wait until Tuesday.

if the altkey column only contains number then it should be converted to a number column

oracle has to overcome a lot of design flaws, and it never mutters about that
in my opinion, that's the biggest flaw, it should actually throw all those errors back at the users

but then everybody would switch oracle off ... as nobody is perfect

But, if you say you should always put data that only contains the characters 0-9 in a NUMBER column, how do you account for data (in the US the easiest ones are zip codes and social security numbers) that can contain leading zeros.  Those wouldn't be stored in a NUMBER column and then everything that displays the data needs to handle reformatting the data, when technically it really isn't a number.

Then take that one step further.  A lot of people that store US zip codes also store Canadian zip codes.  US zip codes are all numeric, but Canadian ones are alpha numeric.  Should I really need 2 zip code fields in my address table to handle both, when one is really all I need?

Thanks everyone for sticking with me on this and helping me find the best solution.

I ended up creating a materialized view & and index on the MV which was super fast for the user.    He was thrilled.