Home » SQL & PL/SQL » SQL & PL/SQL » index was outside the bounds of array
index was outside the bounds of array [message #564655] Mon, 27 August 2012 06:59 Go to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
hello experts,
i have a problem when i execute this query and it takes more than 32 minutes
and after that connection get hanged in toad 9.2.7 and oracle version 11.2.1.0
with this error- index was outside the bounds of array.

SELECT t.ID AS "serial#",
SUM (CASE
WHEN (SELECT MAX (g.test_date)
FROM label1 a LEFT JOIN label2 g ON g.ID = a.ID
) IS NOT NULL
THEN 2
WHEN (SELECT MAX (g.test_date)
FROM label2 g) IS NOT NULL
THEN 1
ELSE 0
END
) AS "labled",
SUM (CASE
WHEN s.accepted_date IS NOT NULL
THEN 1
ELSE 0
END
) AS "Permission",
ROUND
( CASE
WHEN (SUM (CASE
WHEN s.accepted_date IS NOT NULL
THEN 1
ELSE 0
END)
) = 0
THEN 0
ELSE ( SUM (CASE
WHEN s.accepted_date IS NOT NULL
THEN 1
ELSE 0
END
)
* 100
)
END
/ COUNT (t.ID),
0
)
|| '%' AS "accepted Permission%",
SUM (CASE
WHEN s.label_off IS NOT NULL
THEN 1
ELSE 0
END) AS "label_off",
ROUND
( CASE
WHEN (SUM (CASE
WHEN s.label_off IS NOT NULL
THEN 1
ELSE 0
END)) = 0
THEN 0
ELSE ( SUM (CASE
WHEN s.label_off IS NOT NULL
THEN 1
ELSE 0
END)
* 100
)
END
/ COUNT (t.ID),
0
)
|| '%' AS "label_off%"
FROM TEST t LEFT JOIN testserver s ON s.ID = t.ID
GROUP BY t.ID;

i want to know the cause of above error and what would be the solution.

thanks in advance........

[Updated on: Mon, 27 August 2012 07:07]

Report message to a moderator

Re: index was outside the bounds of array [message #564658 is a reply to message #564655] Mon, 27 August 2012 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 60065
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is unreadable.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: index was outside the bounds of array [message #564669 is a reply to message #564658] Mon, 27 August 2012 07:44 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
sorry sir,
here is execution plan

set autotrace on
SELECT   t.ID AS "serial#",
         SUM (CASE
                 WHEN (SELECT MAX (g.test_date)
                         FROM label1 a LEFT JOIN label2 g ON g.ID = a.ID
                              ) IS NOT NULL
                    THEN 2
                 WHEN (SELECT MAX (g.test_date)
                         FROM label2 g) IS NOT NULL
                    THEN 1
                 ELSE 0
              END
             ) AS "labled",
         SUM (CASE
                 WHEN s.accepted_date IS NOT NULL
                    THEN 1
                 ELSE 0
              END) AS "Permission",
            ROUND
               (  CASE
                     WHEN (SUM (CASE
                                   WHEN s.accepted_date IS NOT NULL
                                      THEN 1
                                   ELSE 0
                                END)
                          ) = 0
                        THEN 0
                     ELSE (  SUM (CASE
                                     WHEN s.accepted_date IS NOT NULL
                                        THEN 1
                                     ELSE 0
                                  END
                                 )
                           * 100
                          )
                  END
                / COUNT (t.ID),
                0
               )
         || '%' AS "accepted Permission%",
         SUM (CASE
                 WHEN s.label_off IS NOT NULL
                    THEN 1
                 ELSE 0
              END) AS "label_off",
            ROUND
               (  CASE
                     WHEN (SUM (CASE
                                   WHEN s.label_off IS NOT NULL
                                      THEN 1
                                   ELSE 0
                                END)) = 0
                        THEN 0
                     ELSE (  SUM (CASE
                                     WHEN s.label_off IS NOT NULL
                                        THEN 1
                                     ELSE 0
                                  END)
                           * 100
                          )
                  END
                / COUNT (t.ID),
                0
               )
         || '%' AS "label_off%"
    FROM TEST t LEFT JOIN testserver s ON s.ID = t.ID
GROUP BY t.ID;






Execution Plan
----------------------------------------------------------

--------------------------------------------------------------------------------
----------
| Id  | Operation                        | Name                  | Rows  | Bytes
 | Cost  |
--------------------------------------------------------------------------------
----------
|   0 | SELECT STATEMENT                 |                       |   173 | 84078
 |   821 |
|   1 |  SORT AGGREGATE                  |                       |     1 |    55
 |       |
|   2 |   FILTER                         |                       |       |
 |       |
|   3 |    NESTED LOOPS                  |                       |       |
 |       |
|   4 |     NESTED LOOPS                 |                       |    15 |   825
 |    21 |
|   5 |      TABLE ACCESS FULL           | LABEL1 |    15 |   450
 |     6 |
|   6 |      INDEX UNIQUE SCAN           | LABEL2_ID_PK       |     1 |
 |     0 |
|   7 |     TABLE ACCESS BY INDEX ROWID  | LABEL2             |     1 |    25
 |     1 |
|   8 |  SORT AGGREGATE                  |                       |     1 |    25
 |       |
|   9 |   FILTER                         |                       |       |
 |       |
|  10 |    TABLE ACCESS FULL             | LABEL2             |   239 |  5975
 |   211 |
|  11 |  HASH GROUP BY                   |                       |   173 | 84078
 |   821 |
|  12 |   HASH JOIN RIGHT OUTER          |                       |   173 | 84078
 |   820 |

|  23 |       TABLE ACCESS BY INDEX ROWID| TEST              |     4 |   332
 |     4 |
|  24 |      TABLE ACCESS FULL           | TESTSERVER                | 80468 |  2436
K|   334 |
--------------------------------------------------------------------------------
----------

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
    1513855  consistent gets
          0  physical reads
        260  redo size
       7659  bytes sent via SQL*Net to client
       2263  bytes received via SQL*Net from client
          9  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        116  rows processed

SQL>

this query fetches 116 records
*BlackSwan added {code} tags for EXPLAIN PLAN

[Updated on: Mon, 27 August 2012 09:53] by Moderator

Report message to a moderator

Re: index was outside the bounds of array [message #564673 is a reply to message #564669] Mon, 27 August 2012 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 60065
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First rewrite the query to remove ALL scalar subquery in SELECT clause and put them in WITH clause.

Regards
Michel
Re: index was outside the bounds of array [message #564682 is a reply to message #564673] Mon, 27 August 2012 09:54 Go to previous messageGo to next message
John Watson
Messages: 4873
Registered: January 2010
Location: Global Village
Senior Member
@MC, is it possible for you to take a minute to expand on this, Quote:
rewrite the query to remove ALL scalar subquery in SELECT clause and put them in WITH clause.
I am not cedrtain when sub-query factoring is good or not. Are you saying that one should always do this? As a general best practice? I believe you! I'ld just like to know why.
Re: index was outside the bounds of array [message #564689 is a reply to message #564682] Mon, 27 August 2012 10:26 Go to previous messageGo to next message
mnitu
Messages: 140
Registered: February 2008
Location: Reims
Senior Member
As my understanding sub query factoring is good when the optimizer will avoid re-executing the same (sub) query, probably by materializing the sub-query result set.
But scalar sub-query it is subject of an optimization via a cache mechanism which can be very efficient in this case: the two scalar sub queries are independent of the data contained in the two tables which are used to obtain the result set. Executing them once will suffice.
So this time I doubt Michel advice.
Re: index was outside the bounds of array [message #564694 is a reply to message #564689] Mon, 27 August 2012 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 60065
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The execution is a mess so I speak logically.
The SELECT scalar subqueries are executed for each row of the result set.
Here the scalar subqueries have no relation with this row, they are constant, so no need to execute them more than once.
Due to Oracle internal optimisation (and pga caching) these queries might anyway be executed only once but it is clearer to put them as it in the query itself and do not rely in internal rewriting or optimisation.

Regards
Michel
Re: index was outside the bounds of array [message #564696 is a reply to message #564694] Mon, 27 August 2012 10:47 Go to previous message
John Watson
Messages: 4873
Registered: January 2010
Location: Global Village
Senior Member
Thank you, MC and MN. Makes sense. Sorry to have hijacked your topic, Neetesh.
Previous Topic: log of a stored procedure
Next Topic: implement package utl_mail issue ...
Goto Forum:
  


Current Time: Mon Dec 29 02:55:29 CST 2014

Total time taken to generate the page: 0.14385 seconds