Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Most efficient space usage

    So, we have a workday difference calculator which uses some SQL (I think it's from AskTom) which calculates the number of weekends between 2 dates:

    Code:
    select count(*)
      from ( select rownum rnum
               from all_objects
              where rownum <= trunc(ENDDATE) - trunc(STARTDATE)+1 )
    where to_char( (STARTDATE)+rnum-1, 'DY' ) in ( 'SAT', 'SUN' )
    We recently ran a report with 36,000 rows, which executed the PL/SQL function which contains this SQL stmt, and OEM lit up. So, I figured we could just create a table who's only purpose was to hold rows packed like sardines in each datablock, and trade it for ALL_OBJECTS. Does the following sound like I did everything correct?

    Code:
    CREATE TABLE FORBESC.JUST_ROWS
    (
      F1  NUMBER(1)
    )
    PCTFREE    0
    PCTUSED    99;
    
    begin
     for i in 1 .. 10000
     loop
      insert into just_rows values (substr(to_char(i),1,1));
     end loop;
    end;
    
    select count(*)
      from ( select rownum rnum
               from just_rows
              where rownum <= trunc(ENDDATE) - trunc(STARTDATE)+1 )
    where to_char( (STARTDATE)+rnum-1, 'DY' ) in ( 'SAT', 'SUN' )
    No matter how I tweak JUST_ROWS, this query returns in .01 seconds, so I can't tell that the PCTFREE & PCTUSED values matter.

    Thanks,
    Chuck

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    If you have version 9i or above you could use something like this type of construct:
    Code:
    Select Count(*) From (
        Select Level Rnum From Dual 
             Connect By Level <= Trunc(ENDDATE) - Trunc(STARTDATE)+1 ) 
     Where To_Char( (STARTDATE)+Rnum-1, 'Dy' ) In ( 'Sat', 'Sun' )

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Cool ...
    So, since DUAL is used so frequently, would it be better to create another 1 row table exclusively for this calculation, so as to avoid latch contention?

    Do you have a link to the meaning of "CONNECT BY LEVEL < some_condition"? I can't seem to find anything.

    ---=cf

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    If you're *really* concerned about "latch contention" on dual, you can replace that with a pipelined function.
    Code:
    SQL>
    SQL> create or replace type x as table of number;
      2  /
    
    Type created.
    
    SQL> create or replace function f( y in number ) return x
      2  pipelined is
      3  begin
      4     for i in 1 .. y
      5     loop
      6             pipe row ( i );
      7     end loop;
      8     return;
      9  end;
     10  /
    
    Function created.
    
    SQL> select * from table( f( 10 ) );
    
    COLUMN_VALUE
    ------------
               1
               2
               3
               4
               5
               6
               7
               8
               9
              10
    
    10 rows selected.
    
    SQL>
    However, I would actually use DUAL if I am on 10g and above.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Chuck,
    I don't know what your Production DB is like.
    One of mine contains just over 2,000,000 rows in dba_objects.
    Why are you obsessing over a few MB of disk space when the cost of the disk space is much LESS than the wage/time you've already spent on this?

    Do you suffer from Compulsive Tuning Disorder?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Also Oracle (9i and above) knows that Dual is a special case and doesn't actually access the table, it knows that it is a single column, single row table.
    Last edited by beilstwh; 07-26-07 at 16:31.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    I'm going with the DUAL approach, thanks. Speeds up the entire PL/SQL function by a factor of 2 or 3. In a low-load system.

    And I wasn't worried about using disk space with the JUST_ROWS table approach, I was trying to get as many records into a single 8K block as possible, so table scans would be as quick as possible.

    And I might be a little obsessive in this case since we have to optimize, but I figure the additional knowledge might save time in the future (plus I dig this stuff).

    --=Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •