Forum Stats

  • 3,781,172 Users
  • 2,254,486 Discussions
  • 7,879,604 Comments

Discussions

Denormalizing a column that is a partition index into another format

735819
735819 Member Posts: 22
edited Feb 2, 2010 1:44PM in SQL & PL/SQL
While looking at a poor performing query, we see:
- a timestamp column
- that timestamp is a partition index
- that timestamp columnn is manipulated with a to_char to make a day variable in the sql
- the day variable is used in a join

I see some information at another site saying that doing a denormalization like that on a partition index will force table scans across partitions.

Is it correct that the partition index will not be used if the Day variable is used as a join key?

If so, would a fix be to either remove the Day from the join or create a day dimension table and use a STAR_TRANSFORMATION hit in a join?
Tagged:

Answers

  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    I see no version number
    I see no DDL
    I see no DML
    I see no explain plan report generated using DBMS_XPLAN.DISPLAY

    You will see no help in response to your inquiry.

    Tuning can not be done in a vacuum.
  • 735819
    735819 Member Posts: 22
    Currently running in 10g. It was designed probably in 8i.

    The rest is confidential
  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    JMarkW wrote:
    Currently running in 10g. It was designed probably in 8i.

    The rest is confidential
    That's too bad. You can't provide anything? Not even a made up example that illustrates the problem?

    So it sounds like you are applying a function to a column name and then performing a join based on the result of this function. If that is correct, you may be able to create what's called a Function-Based Index. For example this is a sample FBI:
    CREATE INDEX TEST_FBI ON TEST (TO_CHAR(TSTAMP,'YYYYMMDD'));
    That may provide some immediate benefit to your situation. Don't forget to gather statistics on the table after you create this index.
  • 735819
    735819 Member Posts: 22
    I'm looking to verify information from another source. So far I have not seen the same information anywhere else.

    http://www.orafaq.com/tuningguide/partition%20prune.html

    Never denormalize the partition key into other columns, because queries on those other columns will not partition prune. eg. If calendar_date were the partition key, do not create another column such as calendar_month that is derived from calendar_date. Instead, create a date lookup (dimension) table that does the denormalization for you and use the STAR_TRANSFORMATION hint. eg.

    SELECT /*+ STAR_TRANSFORMATION*/ *
    FROM big_partitioned a, months b
    WHERE b.calendar_month = '200304'
    AND a.calendar_date BETWEEN b.month_start and b.month_end
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    Two thoughts.

    The first is that your explanation is nonsensical. Lets assume the following query is confidential.

    SELECT secret_password FROM our_confidential_information WHERE dept_name = 'CIA';

    Fine. But this query is not:

    SELECT test_column FROM test_table WHERE dept_name = 'CATS';

    The second thought is that you should not ask for help if you are not willing to post sufficient information for someone to help you.
  • 735819
    735819 Member Posts: 22
    This is more like it:

    tableA - partitioned by month using a timestamp (Oracle timestamp date type). The entire table is 10 terabytes in size with 60 partitions.

    tableB - indexed by a year/week field (ie. 201007). This table is small without partitions.

    The join is done by

    select count(0)
    from tableA a,
    tableB b
    where to_char((timestamp,'IYYYIW')) = b.yearweek
  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    Instead of applying a function to the column located in the large table, why not apply a function to the column in the smaller table?
  • 735819
    735819 Member Posts: 22
    edited Feb 2, 2010 12:43PM
    That is what I asked the app dev personnel .....

    Edited by: JMarkW on Feb 2, 2010 12:43 PM
  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    edited Feb 2, 2010 1:06PM
    Maybe something like this?
    SELECT  COUNT(0)
    FROM    TABLEA A
    ,       TABLEB B
    WHERE   A.TSTAMP        >=      TRUNC(TO_DATE(SUBSTR(YEARWEEK,1,4) || '0105','YYYYMMDD'),'IYYY') + (SUBSTR(YEARWEEK,5,2) - 1) * 7
    AND     A.TSTAMP        <       TRUNC(TO_DATE(SUBSTR(YEARWEEK,1,4) || '0105','YYYYMMDD'),'IYYY') + SUBSTR(YEARWEEK,5,2) * 7   
    Please note that I haven't tested it thoroughly.

    What would may be even better is to skip this function approach altogether. Have you thought about storing WEEK_START and WEEK_END in your TABLEB as separate columns? That way these values could both be indexed and it might provide even better access.

    Edited by: Centinul on Feb 2, 2010 1:04 PM
    Centinul
  • 735819
    735819 Member Posts: 22
    Very nice SQL!

    It was correct except for need a - 1 after the second trunc.

    We can work with that SQL to test the how the optomizer selects the join path
This discussion has been closed.