• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

NVL Full table

How  I can prevent full access to the table?

 Explain Plan
SELECT A.NIS,A.PER_NOMBRE,A.PER_PRIMER_APELLIDO,A.TIPO_DOCUMENTO,A.NUM_DOC_IDENTIDAD,RGA.PRF_ID,RGA.FIC_ID
  FROM MATRICULA.REGISTRO_ACADEMICO RGA, 
       COMUN.PERSONA A
 WHERE RGA.NIS               = A.NIS
   AND A.NIS                 = NVL(:B8, A.NIS)
   AND A.PER_NOMBRE          LIKE NVL(:B7, '%')
   AND A.PER_PRIMER_APELLIDO LIKE NVL(:B6, '%')
   AND A.TIPO_DOCUMENTO      = NVL(:B5, A.TIPO_DOCUMENTO)
   AND A.NUM_DOC_IDENTIDAD   LIKE NVL(:B4, '%')
   AND RGA.RGA_ESTADO        LIKE NVL(TO_CHAR(:B3), '%')
   AND RGA.PRF_ID            = NVL(:B2, RGA.PRF_ID)
   AND RGA.FIC_ID            = NVL(:B1, RGA.FIC_ID)
   AND ROWNUM <= 500

Open in new window

0
jairdur
Asked:
jairdur
  • 17
  • 13
  • 6
  • +1
2 Solutions
 
slightwv (䄆 Netminder) Commented:
Might be an optimizer 'bug'.

Try a hint:

SELECT /*+ FIRST_ROWS */ A.NIS
...

0
 
sdstuberCommented:
create an index on persona

I'd have to know your data to recommend the most effective index.

but to start with you could try using a composite index on all of the columns in the where clause
0
 
sdstuberCommented:
ah, on second glance  the NVL is the problem  you're applying a function to a column

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
slightwv (䄆 Netminder) Commented:
To force the used of L_PER_PK, you can force an index hint or remove the Optimizer (not really recommended) with a RULE hint:

SELECT /*+ RULE*/ A.NIS
...
0
 
jairdurAuthor Commented:
slightwv:  is equal thanks

sdstuber: the table have indexes see please
0
 
jairdurAuthor Commented:
wait
0
 
jairdurAuthor Commented:
slightwv: HINTS no applay
0
 
slightwv (䄆 Netminder) Commented:
>>slightwv:  is equal thanks

Even using RULE based?
0
 
jairdurAuthor Commented:
exact sdstuber

what i do?
0
 
jairdurAuthor Commented:
Oracle 10g Optimizador ALL_ROWS
0
 
sdstuberCommented:
>>> sdstuber: the table have indexes see please


yes, I know.  sorry.  I should have "create a better index"  not "create an index"

your  use of a function on a.nis  prevents an index on a.nis from being used.

specifically   -    NVL(:b8,a.nis)  

for the optimizer    
   this
       AND A.NIS                 = NVL(:B8, A.NIS)
is significantly different than
       AND A.NIS                 = 7355815
0
 
sdstuberCommented:
I shold probably elaborate...

       AND A.NIS                 = NVL(:B8, A.NIS)

you might look at this as a query that should use an index because A.NIS is the search criteria.
However,  you ALSO have A.NIS inside the function call.

so,  while you might find it convenient to think of this condition as...

"lookup when there is a value, otherwise don't do anything "

in reality you have  "do function call on column,  check if function call on column is the same as other value, where other value is itself the same column "



0
 
jairdurAuthor Commented:
NULL?
0
 
jairdurAuthor Commented:
AND A.NIS                 = NVL(NULL/*:B8*/, A.NIS)
0
 
jairdurAuthor Commented:
review the query
0
 
jairdurAuthor Commented:
SELECT A.NIS,A.PER_NOMBRE,A.PER_PRIMER_APELLIDO,A.TIPO_DOCUMENTO,A.NUM_DOC_IDENTIDAD,RGA.PRF_ID,RGA.FIC_ID
  FROM MATRICULA.REGISTRO_ACADEMICO RGA,
       COMUN.PERSONA A
 WHERE RGA.NIS               = A.NIS
   AND A.NIS                 = NVL(:B8, A.NIS)
   AND A.PER_NOMBRE          LIKE NVL(:B7, '%')
   AND A.PER_PRIMER_APELLIDO LIKE NVL(:B6, '%')
   AND A.TIPO_DOCUMENTO      = NVL(:B5, A.TIPO_DOCUMENTO)
   AND A.NUM_DOC_IDENTIDAD   LIKE NVL(:B4, '%')
   AND RGA.RGA_ESTADO        LIKE NVL(TO_CHAR(:B3), '%')
   AND RGA.PRF_ID            = NVL(:B2, RGA.PRF_ID)
   AND RGA.FIC_ID            = NVL(:B1, RGA.FIC_ID)
   AND ROWNUM <= 500
0
 
jairdurAuthor Commented:
the SQL statement has 8 possibilities of making the request or combinations thereof. See?
0
 
slightwv (䄆 Netminder) Commented:
The issue is in 10g, as soon as the optimizer sees a function, the NVL, on an indexed column, it won't use the index.

We are both working to see if there is a way around this.

There is in 11gR2.
0
 
jairdurAuthor Commented:
please thanks
0
 
sdstuberCommented:
you may want to use dynamic sql to construct your query
to exclude conditions where the parameter is NULL

0
 
sdstuberCommented:
what indexes do you have on persona?  is the index on NIS the only one?
0
 
jairdurAuthor Commented:
sdstuber: SAY what indexes do you have on persona?  is the index on NIS the only one? NO
-- Create table
create table COMUN.PERSONA
(
  NIS                  NUMBER(12),
  TIPO_DOCUMENTO       VARCHAR2(3),
  NUM_DOC_IDENTIDAD    VARCHAR2(20),
  PER_NOMBRE           VARCHAR2(50),
  PER_PRIMER_APELLIDO  VARCHAR2(50),
  PER_SEGUNDO_APELLIDO VARCHAR2(50),
  PER_CORREO_E         VARCHAR2(50)
)
partition by hash (NIS)
(
  partition PER_NIS_P1
    tablespace PERSONA_DAT_01,
  partition PER_NIS_P2
    tablespace PERSONA_DAT_02,
  partition PER_NIS_P3
    tablespace PERSONA_DAT_01,
  partition PER_NIS_P4
    tablespace PERSONA_DAT_02
);
-- Create/Recreate primary, unique and foreign key constraints 
alter table COMUN.PERSONA  add constraint PER_PK primary key (NIS);
alter table COMUN.PERSONA  add constraint PER_NUM_DOCUMENTO_UK unique (NUM_DOC_IDENTIDAD, TIPO_DOCUMENTO);
alter table COMUN.PERSONA  add constraint USR_PER_FK foreign key (NIS)
  references COMUN.USUARIO (NIS);
-- Create/Recreate check constraints 
alter table COMUN.PERSONA  add constraint NUM_DOC_IDENTIDAD_PER_NN
  check ("NUM_DOC_IDENTIDAD" IS NOT NULL);
alter table COMUN.PERSONA  add constraint PER_CORREO_E_NN
  check ("PER_CORREO_E" IS NOT NULL);
alter table COMUN.PERSONA  add constraint PER_NOMBRE_NN
  check ("PER_NOMBRE" IS NOT NULL);
alter table COMUN.PERSONA  add constraint PER_PRIMER_APELLIDO_NN
  check ("PER_PRIMER_APELLIDO" IS NOT NULL);
alter table COMUN.PERSONA  add constraint PER_TIPO_DOCUMENTO_CK
  check (TIPO_DOCUMENTO IN ('CC', 'TI', 'CE', 'NIT', 'NCS','NA', 'RC','NIS','RUI','PS','DNI'));
alter table COMUN.PERSONA  add constraint TIPO_DOCUMENTO_PER_NN
  check ("TIPO_DOCUMENTO" IS NOT NULL);
alter table COMUN.PERSONA  add constraint USR_PER_NN
  check ("NIS" IS NOT NULL);
-- Create/Recreate indexes 
create index COMUN.I_PER_NOMBRE on COMUN.PERSONA (PER_NOMBRE);
create unique index COMUN.I_PER_NUM_DOCUMENTO_UK on COMUN.PERSONA (NUM_DOC_IDENTIDAD, TIPO_DOCUMENTO);
create unique index COMUN.I_PER_PK on COMUN.PERSONA (NIS);
create index COMUN.I_PER_PRIMER_APELLIDO on COMUN.PERSONA (PER_PRIMER_APELLIDO);

Open in new window

0
 
sdstuberCommented:
in that case I recommend going with dynamic sql.
that way your parameters will generate a plan specific to the available indexes that apply to your parameterized conditions
0
 
slightwv (䄆 Netminder) Commented:
Not as good as dynamic SQL but...

In my tests this isn't as good as the full index but appears to be slightly better of a plan than the FULL TABLE SCAN.

Your mileage may vary.

You might need to add another parameter depending on what is calling this.

...
 AND A.NIS                 = DECODE(:B8, null, A.NIS, :B8)
...
0
 
gajmpCommented:
Try this


AND (A.NIS  = :B8 or (:B8 is null and 1 = 1))
0
 
sdstuberCommented:
the optimizer will remove the 1=1

and it will simply become...

AND (A.NIS  = :B8 or :B8 is null)


or more likely  it will do the null evaluation first...

AND (:B8 is null OR A.NIS = :B8)


in any case,  the 1=1 bit isn't helpful.   (test it, you can see for yourself)

there is a trick for doing that to make dynamic sql generation more convienient,  but without context it doesn't do anything

0
 
gajmpCommented:
yes optimizer wont evaluate 1 = 1.

:B8 is null - It won't affect the performance.

A.NIS  = :B8 - I am sure optimizer will consider the index.
0
 
sdstuberCommented:
actually it's likely the index will NOT be used

test it, you'll see  a Filter Predicate  of (:B8 is null OR A.NIS = :B8)


the "OR" will negate the index
0
 
jairdurAuthor Commented:
Serñor slightwv: So far the DECODE has been effective

sdstuber: That might work but with UNION , right?
0
 
sdstuberCommented:
>>> That might work


what is "that"  ?
0
 
jairdurAuthor Commented:
Could
0
 
sdstuberCommented:
I have no idea what "could" means ?


what are you asking me to evaluate?  Post some code or a link to a post or a post id or something that indicates what you're referring to
0
 
jairdurAuthor Commented:

This (:B8 is null OR A.NIS = :B8) could be but with  4  UNIONs
0
 
sdstuberCommented:
yes it could work as far as functionality goes, but it's not going to help with indexes, and, with 4 unions, you're hitting the same data again and again.
0
 
jairdurAuthor Commented:

Sorry 6 UNION

You tried it?
0
 
sdstuberCommented:
no I did not.  I tested on smaller scale and extrapolated to 4 and 6 (or more)

I could be wrong though.  Also, it will depend somewhat on your particular settings.
If you have more memory, particularly more hash/sort space the optimizer has additional options it can attempt and plans can change quite a bit.

In this particular case,  with the NULL/ NOT NULL check  you're limiting what the optimizer can do on its own.
0
 
slightwv (䄆 Netminder) Commented:
I would like to point out that you do not have to run the SQL to generate the execution plan.  Then you can actually test a variety of different SQL without actually impacting the system.

Test whatever SQL you think might work for you.

Just add: "explain plan for" in front of your select.

I prefer sqlplus:

explain plan for select sysdate from dual;

Then display it with:
select * from table(dbms_xplan.display);

0
 
jairdurAuthor Commented:
DECODE no full scan
SQL Dynamic is good

Thank Señores
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 17
  • 13
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now