7 Replies Latest reply: May 20, 2014 5:34 PM by SomeoneElse RSS

    Index to get data from a parent table?

    c5a2496e-00b3-475e-aa59-1f61bc3b99af

      Is there a way to index a child table with a function to include a colum from a parent table?

       

      Example:

       

      I want to see how many children live in a zipcode (and their address is listed only with their parents)

       

      Select count(*)

      from child c, parent p

      where c.parent_id=p.parent_id

      and p.zipcode="12345"

       

      Is there a way to make an index of children with a function that gets their parents zipcode?

       

      Thanks for the help?

        • 1. Re: Index to get data from a parent table?
          Frank Kulash

          Hi,

           

          I'm not sure I understand the question.

           

          An index on the child table can only reference columns from the child table.  You can't have an index on the child table that depends on any information from the parent table.

          There are Function Based Indexes, but they can only involve deterministic functions and constants (and, of course, the columns in the table).  For example, the child table could have an index on UPPER (last_name), where last_name is a column in the table, but it couldn't have an index on get_zip (parent_id), where get_zip is a user-defined function that queries the parent table.

           

          You could, possibly, replicate (that is, copy) data from the parent table in the child table, so that the same information is stored in both tables; then you could index it in the child table, and sometimes avoid joins.  This is problem-fraught, and it violates 2nd Normal Form, but some people do it anyway, because it can improve performance.

          • 2. Re: Index to get data from a parent table?
            SomeoneElse

            What problem are you trying to solve?

             

            What's wrong with the query exactly as you posted it?

            • 3. Re: Index to get data from a parent table?
              c5a2496e-00b3-475e-aa59-1f61bc3b99af

              My issues is that I have very large tables, ands the criteria I need in my queries is not specific enough in any one column to narrow down the records to a manageable amount.

              Unfortunately these tables are in a vendor-supplied application, so I have no ability to modify the tables. But I may be able to create an index.

               

              Maybe I should have used State instead of Zipcode in the example, which would have been closer to the problem. Neither criteria is very specific.

               

              -so the child table and the parent tables have over 100 million records each.

              Each of the criteria narrows down the results by only 1/100th.

              So no matter what table the optimizer starts in, it has about 1 million rows that it needs to join to the other table before applying the next criteria.

              • 4. Re: Index to get data from a parent table?
                K.S.I.

                Hi.

                 

                Before creating an index, it is possible to carry out an previously assessment

                 

                ORACLE-BASE - Virtual Indexes

                 

                 

                One option :

                 

                Collect the report on sql-queries  with a low productivity, provide all necessary metrics and let the developer of this program will make: patch, hot-fix  of

                making reorganization  of structures (for example a partitioning) and etc.

                 

                 

                Alternative:

                 

                Collect statistics.

                Estimate distribution of data.

                Define types of inquiries proceeding from history of performance of inquiries,

                and only after  - to create of  necessary indexes.

                • 5. Re: Index to get data from a parent table?
                  c5a2496e-00b3-475e-aa59-1f61bc3b99af


                  Thanks for the reply, but that doesnt address my question.

                   

                  I am trying to find some way to get index-like performance on two fields from different tables.

                  This is speed up queries that join large amounts of data.

                  • 6. Re: Index to get data from a parent table?
                    SomeoneElse

                    I still don't see what actual *problem* you're having.

                     

                    If this is just a tuning request, you'll need to post the information requested in this thread: HOW TO: Post a SQL statement tuning request - template posting

                     

                    Otherwise this is all vague.

                    • 7. Re: Index to get data from a parent table?
                      rp0428
                      An index on the child table can only reference columns from the child table.  You can't have an index on the child table that depends on any information from the parent table.

                      There are Function Based Indexes, but they can only involve deterministic functions and constants (and, of course, the columns in the table).  For example, the child table could have an index on UPPER (last_name), where last_name is a column in the table, but it couldn't have an index on get_zip (parent_id), where get_zip is a user-defined function that queries the parent table.

                      Well - you 'could' have such a functional index if you made it deterministic but it isn't advised since if the parent zip_code changed it wouldn't really be deterministic and the change wouldn't be reflected in the index.

                       

                      That aside this code will work correctly if the zip_code data doesn't change:

                      CREATE TABLE parent (COL1 number primary key, zip VARCHAR2(5))
                          
                      create table child (col1 number, parent_key number);

                       

                      create or replace function get_zip (p_key number) return varchar2 deterministic as
                      v_zip varchar2(5);
                      begin
                        select zip into v_zip from parent where col1 = p_key;
                        return v_zip;
                      end;

                      /

                       

                      create index child_zip on child (get_zip(parent_key));