#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    2
    Rep Power
    0

    Oracle query optimisation on join with a view on a huge table


    Hello!

    Please help

    this is my scenario:

    I have this table

    create table ACTIONARI_ARH
    (
    actionar_id NUMBER(10) not null,
    id VARCHAR2(20) not null,
    id_2 VARCHAR2(20),
    tip VARCHAR2(1),
    nume VARCHAR2(100),
    prenume VARCHAR2(100),
    adresa VARCHAR2(200),
    localitate VARCHAR2(50),
    judet VARCHAR2(2),
    tara VARCHAR2(3),
    cert_deces VARCHAR2(20),
    data_registru DATE not null,
    user_modif VARCHAR2(30),
    data_modif DATE,
    rezident VARCHAR2(1)
    );

    -- Create/Recreate primary, unique and foreign key constraints
    alter table ACTIONARI_ARH
    add constraint ACTIONARI_ARH_PK primary key (ACTIONAR_ID, DATA_REGISTRU)
    using index;

    -- Create/Recreate indexes
    create index ACTIONARI_ARH_IDX on ACTIONARI_ARH (ID) compress;
    create index ACTIONARI_ARK_ACTID on ACTIONARI_ARH (ACTIONAR_ID) compress;
    create index ACTIONARI_ARK_DATA on ACTIONARI_ARH (DATA_REGISTRU) compress;


    and this view

    CREATE OR REPLACE VIEW ACTIONARI AS
    SELECT "ACTIONAR_ID","ID","ID_2","TIP","NUME","PRENUME","ADRESA","LOCALITATE","JUDET","TARA","CERT_DECES"," DATA_REGISTRU" Data_operare,"USER_MODIF","DATA_MODIF","REZIDENT"
    FROM (
    select "ACTIONAR_ID","ID","ID_2","TIP","NUME","PRENUME","ADRESA","LOCALITATE","JUDET","TARA","CERT_DECES"," DATA_REGISTRU","USER_MODIF","DATA_MODIF","REZIDENT",
    row_number() OVER (PARTITION BY ACTIONAR_ID ORDER BY DATA_REGISTRU desc) ACTIV
    from actionari_arh
    )
    where activ = 1;


    The table has about 30 milion records and holds persons names, addresses, personal id (id), and internal id(actionar_id) and date when a new adress has been added.

    The view is about getting only the most recent info for one person (actionar_id).


    if i run a

    a) select * from actionari a where a.actionar_id = 'nnnnnnn', result is returned immediatly, oracle uses index and does not do a full table scan.
    b) select * from actionari a where a.actionar_id in ('nnnnnnn','mmmmmm','ooooooo'), result is returned immediatly, oracle uses index and does not do a full table scan.

    my problem when i use this view in a join.

    let's assume i have another table with no more than 500 records, something like

    create table SMALL_TABLE
    (
    actionar_id NUMBER(10) not null,
    ......
    );


    and if i run

    select *
    from SMALL_TABLE s

    join actionari a
    on a.actionar_id = s.actionar_id;

    it takes like forever to process, forever means 1~3 minutes.
    by looking at the execution plan, oracle does a full table scan, creates the view for all unique 7milion persons, and only then joins the result with the actionar_is's in the small table and returns the desired 500 record result.

    i am using oracle 10g.

    Thank you, i apreciate all the help i can get;
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    58
    Rep Power
    5
    Infinity,

    Could you post the execution plan for the join ?

    Did you analyze both tables and their indexes ?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2011
    Posts
    2
    Rep Power
    0
    select *
    from registre_top t

    join actionari a
    on a.ACTIONAR_ID = t.actionar_id

    where t.registru_id = 124;

    this returnes 1000 rows from the REGISTRE_TOP table. The execution plan is as follows, after the statistics have been calculated for the entire schema;



    SELECT STATEMENT, GOAL = ALL_ROWS 657191 2622 930810
    MERGE JOIN 657191 2622 930810
    VIEW SIF4_ACT 657181 24395428 8294445520
    WINDOW SORT PUSHED RANK 657181 24395428 2512729084
    TABLE ACCESS FULL SIF4_ACT ACTIONARI_ARH 83288 24395428 2512729084
    SORT JOIN 10 1161 17415
    TABLE ACCESS FULL SIF4_ACT REGISTRE_TOP 9 1161 17415

IMN logo majestic logo threadwatch logo seochat tools logo