Why using globe temp table (GTT)?
Faster, improve performance.
http://www.dba-oracle.com/t_temporary_tables_sql.htm
http://www.dba-oracle.com/t_sql_rewrite_temporary_tables.htm
What is the difference between 'on commit preserve rows' and 'on commit delete rows' for GTT?
The ON COMMIT PRESERVE ROWS makes this a session based temporary table. rows will stay in this table until a logoff.
The ON COMMIT DELETE ROWS makes this a transaction based temp table. When you commit -- the rows disappear.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:48812348054
What is explain plan and how to read it?
It tells you how oracle processes the query. Read it form inner right to outer left.
http://www.oracle-base.com/articles/8i/ExplainPlanUsage.php
http://www.orafaq.com/node/1420
Why use bulk collect?
Improve performance.
http://www.dba-oracle.com/t_oracle_bulk_collect.htm
Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts
Various Sizes And Lengths In Database
But, it is suggested to use 30 as maximum column name length in SQL Server.
Feature, SQL Server 2000, Oracle 9i Database
database name length, 128, 8
column name length, 128, 30
index name length, 128, 30
table name length, 128, 30
view name length, 128, 30
stored procedure name length, 128, 30
max columns per index, 16, 32
max char() size 8000, 2000
max varchar() size, 8000, 4000
max columns per table, 1024, 1000
max table row length, 8036, 255000
max query size, 16777216, 16777216
recursive subqueries, 40, 64
constant string size in SELECT, 16777207, 4000
constant string size in WHERE, 8000, 4000
http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm
Conservative Forecast Based On Historic Data
You can always use mean on the historic data as your forecast, which is reasonable. However, this is radical, especially when not consider the probability distribution. To be conservative, you may need to add a standard deviation (σ):
Mean + σ
Or
Mean - σ
In general, 1 standard deviation is good enough. In normally distributed data, 1σ means the side coverage of 34.1%, accounts for 84.1% of total population.
If there is no noise data within the dataset, arithmetic mean (average) can be used. However, if there is noise data, interquartile mean (IQM) is recommended. Accordingly, the standard deviation should be also based on the interquartile range (middle 50%).
In MySQL, the built-in function is STD(expr). In Oracle, it is STDDEV(expr) and in T-SQL, it is STDEVP(expr).
http://en.wikipedia.org/wiki/Standard_deviation
http://koncordpartners.blogspot.com/2010/11/practical-mean.html
http://oreilly.com/catalog/sqlnut/chapter/ch04.html
http://www.devguru.com/technologies/t-sql/7097.asp
Mean + σ
Or
Mean - σ
In general, 1 standard deviation is good enough. In normally distributed data, 1σ means the side coverage of 34.1%, accounts for 84.1% of total population.
If there is no noise data within the dataset, arithmetic mean (average) can be used. However, if there is noise data, interquartile mean (IQM) is recommended. Accordingly, the standard deviation should be also based on the interquartile range (middle 50%).
In MySQL, the built-in function is STD(expr). In Oracle, it is STDDEV(expr) and in T-SQL, it is STDEVP(expr).
http://en.wikipedia.org/wiki/Standard_deviation
http://koncordpartners.blogspot.com/2010/11/practical-mean.html
http://oreilly.com/catalog/sqlnut/chapter/ch04.html
http://www.devguru.com/technologies/t-sql/7097.asp
Pivot Large Table With Dynamic Columns
In general, it is recommended to use SUM(CASE) GROUP BY method to pivot rows into columns. For details why recommended such, please refer to http://koncordpartners.blogspot.com/2010/03/pivot-turning-rows-into-columns.html. However, in two circumstances SUM(CASE) GROUP BY method does not work:
1. The column numbers are too many. By using SUM(CASE) GROUP BY method, one must know every column’s name.
2. The column names are unknown when generate queries. Usually this could be happened with column names are result from another SELECT statement.
In fact, these two circumstances do always emerge together. In this situation, built-in PIVOT function in Oracle and SQL Server is the only option. In Ms Access, it is TRANSFORM, which is even better in comparison to Oracle and SQL Server, because it accept the list of column names directly from the SELECT statement. Following example is using T-SQL, and the scenario is to draw a matrix table summarizing the total air ambulance flights between airports within a state for past ten year. The involved records are around 500,000.
The PIVOT function is easy:
SELECT *
FROM
(
SELECT sendport AS Sending_Airport
, receiveport AS Receiving_Airport
, 1 AS InterimCounter
FROM dw_Legs
) AS Inner_Q
PIVOT
(
SUM(InterimCounter)
FOR Receiving_Airport IN ([The list of receiving airport])
) AS Pivot_Q
The problem is “The list of receiving airport” must be selected from airport table, and very unfortunately, T-SQL does not accept it as an outcome of SELECT statement. So, dynamic query is the only option left. We human just make our life so miserable by disallow this and disallow that.
Another issue is not all airports receive planes from every rest airports, which means “The list of receiving airport” should not be directly from airport table, rather is should be from the intermediate queries. So, for presentation purpose, a temporary table is the best solution, which is also in accordance with the design of SQL Server.
Step 1
CREATE VIEW V_Airport_Matrix
AS
SELECT sendport AS Sending_Airport
, receiveport AS Receiving_Airport
, 1 AS InterimCounter
FROM dw_Legs
GO
Step 2
Prepare dynamic queries:
DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns + ',[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR) + ']', '[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR)+ ']')
FROM
(
SELECT DISTINCT Receiving_Airport
FROM V_Airport_Matrix
) AS base_Q
Why LTRIM(RTRIM()) is needed? Because the length of the VARCHAR string becomes critical since VARCHAR(MAX) does have limit. And very often, VARCHAR(MAX) does not meet our need here. And, the text, ntext, and image data types are invalid for local variables. Following is the send part of Step 2:
DECLARE @query VARCHAR(MAX)
SET @query =
'
SELECT *
FROM
(
SELECT *
FROM V_Airport_Matrix
) AS Inner_Q
PIVOT
(
SUM(InterimCounter)
FOR Receiving_Airport IN (' + @columns + ')
) AS Pivot_Q
'
What about your query does exceed the VARCHAR(MAX) limit? You will need to shorten the query by cutting them into pieces:
DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns + ',[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR) + ']', '[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR)+ ']')
FROM
(
SELECT DISTINCT Receiving_Airport
FROM V_Airport_Matrix
WHERE Receiving_Airport LIKE 'A%'
OR Receiving_Airport LIKE 'B%'
OR Receiving_Airport LIKE 'C%'
OR Receiving_Airport LIKE 'D%'
OR Receiving_Airport LIKE 'E%'
OR Receiving_Airport LIKE 'F%'
OR Receiving_Airport LIKE 'G%'
OR Receiving_Airport LIKE 'H%'
OR Receiving_Airport LIKE 'I%'
OR Receiving_Airport LIKE 'J%'
OR Receiving_Airport LIKE 'K%'
OR Receiving_Airport 'L%'
) AS base_Q
Then, execute one by one before union together.
Step 3
To execute the queries:
EXECUTE(@query)
http://blog-mstechnology.blogspot.com/2010/06/t-sql-pivot-operator-with-dynamic.html
1. The column numbers are too many. By using SUM(CASE) GROUP BY method, one must know every column’s name.
2. The column names are unknown when generate queries. Usually this could be happened with column names are result from another SELECT statement.
In fact, these two circumstances do always emerge together. In this situation, built-in PIVOT function in Oracle and SQL Server is the only option. In Ms Access, it is TRANSFORM, which is even better in comparison to Oracle and SQL Server, because it accept the list of column names directly from the SELECT statement. Following example is using T-SQL, and the scenario is to draw a matrix table summarizing the total air ambulance flights between airports within a state for past ten year. The involved records are around 500,000.
The PIVOT function is easy:
SELECT *
FROM
(
SELECT sendport AS Sending_Airport
, receiveport AS Receiving_Airport
, 1 AS InterimCounter
FROM dw_Legs
) AS Inner_Q
PIVOT
(
SUM(InterimCounter)
FOR Receiving_Airport IN ([The list of receiving airport])
) AS Pivot_Q
The problem is “The list of receiving airport” must be selected from airport table, and very unfortunately, T-SQL does not accept it as an outcome of SELECT statement. So, dynamic query is the only option left. We human just make our life so miserable by disallow this and disallow that.
Another issue is not all airports receive planes from every rest airports, which means “The list of receiving airport” should not be directly from airport table, rather is should be from the intermediate queries. So, for presentation purpose, a temporary table is the best solution, which is also in accordance with the design of SQL Server.
Step 1
CREATE VIEW V_Airport_Matrix
AS
SELECT sendport AS Sending_Airport
, receiveport AS Receiving_Airport
, 1 AS InterimCounter
FROM dw_Legs
GO
Step 2
Prepare dynamic queries:
DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns + ',[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR) + ']', '[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR)+ ']')
FROM
(
SELECT DISTINCT Receiving_Airport
FROM V_Airport_Matrix
) AS base_Q
Why LTRIM(RTRIM()) is needed? Because the length of the VARCHAR string becomes critical since VARCHAR(MAX) does have limit. And very often, VARCHAR(MAX) does not meet our need here. And, the text, ntext, and image data types are invalid for local variables. Following is the send part of Step 2:
DECLARE @query VARCHAR(MAX)
SET @query =
'
SELECT *
FROM
(
SELECT *
FROM V_Airport_Matrix
) AS Inner_Q
PIVOT
(
SUM(InterimCounter)
FOR Receiving_Airport IN (' + @columns + ')
) AS Pivot_Q
'
What about your query does exceed the VARCHAR(MAX) limit? You will need to shorten the query by cutting them into pieces:
DECLARE @columns VARCHAR(MAX)
SELECT @columns = COALESCE(@columns + ',[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR) + ']', '[' + CAST(LTRIM(RTRIM(Receiving_Airport)) AS VARCHAR)+ ']')
FROM
(
SELECT DISTINCT Receiving_Airport
FROM V_Airport_Matrix
WHERE Receiving_Airport LIKE 'A%'
OR Receiving_Airport LIKE 'B%'
OR Receiving_Airport LIKE 'C%'
OR Receiving_Airport LIKE 'D%'
OR Receiving_Airport LIKE 'E%'
OR Receiving_Airport LIKE 'F%'
OR Receiving_Airport LIKE 'G%'
OR Receiving_Airport LIKE 'H%'
OR Receiving_Airport LIKE 'I%'
OR Receiving_Airport LIKE 'J%'
OR Receiving_Airport LIKE 'K%'
OR Receiving_Airport 'L%'
) AS base_Q
Then, execute one by one before union together.
Step 3
To execute the queries:
EXECUTE(@query)
http://blog-mstechnology.blogspot.com/2010/06/t-sql-pivot-operator-with-dynamic.html
Copy Only the Structure of A Table
This old little trick had been asked for so many times. So, it might be a good itea to post it on the blog.
In Oracle:
CREATE TABLE NEW_TAB AS
SELECT *
FROM OLD_TAB
WHERE 0 = -1
In SQL Server:
SELECT *
INTO NEW_TAB
FROM OLD_TAB
WHERE 0 = -1
The key point is WHERE condition. Because this condition is never to be met, so there is no records actually being selected. Someone might like WHERE 1 = 2, however, 1 or 2 can be referred to column one and column two. If by chance the value in column one just equals to column two, haha, you will get some data.
In Oracle:
CREATE TABLE NEW_TAB AS
SELECT *
FROM OLD_TAB
WHERE 0 = -1
In SQL Server:
SELECT *
INTO NEW_TAB
FROM OLD_TAB
WHERE 0 = -1
The key point is WHERE condition. Because this condition is never to be met, so there is no records actually being selected. Someone might like WHERE 1 = 2, however, 1 or 2 can be referred to column one and column two. If by chance the value in column one just equals to column two, haha, you will get some data.
A Good IT Certification Study Website
IT Exams is very good IT certification study website, if not the best. It links to a bunch of text books and dump tests, which are complete free. Unfortunately, it only has Java and Oracle certification information.
IT Exams' address is http://itexams.weebly.com/.
IT Exams' address is http://itexams.weebly.com/.
Temporarily Disable and Re-enable the Constraints in Oracle
SQL command script files to disable and enable all constraints:
Disable:
set feedback off
set verify off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_disable.sql
select 'spool igen_disable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' DISABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_disable.sql;
exit
/
Enable:
set feedback off
set verify off
set wrap off
set echo off
prompt Finding constraints to enable...
set termout off
set lines 120
set heading off
spool tmp_enable.sql
select 'spool igen_enable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' ENABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
/
select 'exit;' from dual;
set termout on
prompt Enabling constraints now...
set termout off
@tmp_enable;
!rm -i tmp_enable.sql;
exit
/
Scripts in PL/SQL to disable and enable all constraints:
Disable:
BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'ENABLED'
ORDER BY c.constraint_type DESC
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || i.owner || '.' || i.table_name || ' disable constraint ' || i.constraint_name);
END LOOP;
END;
/
Enable:
BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'DISABLED'
ORDER BY c.constraint_type
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP;
END;
/
Disable:
set feedback off
set verify off
set echo off
prompt Finding constraints to disable...
set termout off
set pages 80
set heading off
set linesize 120
spool tmp_disable.sql
select 'spool igen_disable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' DISABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
select 'exit;' from dual;
set termout on
prompt Disabling constraints now...
set termout off
@tmp_disable.sql;
exit
/
Enable:
set feedback off
set verify off
set wrap off
set echo off
prompt Finding constraints to enable...
set termout off
set lines 120
set heading off
spool tmp_enable.sql
select 'spool igen_enable.log;' from dual;
select 'ALTER TABLE '||substr(c.table_name,1,35)||' ENABLE CONSTRAINT '||constraint_name||' ;'
from user_constraints c join user_tables u on c.table_name = u.table_name;
/
select 'exit;' from dual;
set termout on
prompt Enabling constraints now...
set termout off
@tmp_enable;
!rm -i tmp_enable.sql;
exit
/
Scripts in PL/SQL to disable and enable all constraints:
Disable:
BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'ENABLED'
ORDER BY c.constraint_type DESC
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || i.owner || '.' || i.table_name || ' disable constraint ' || i.constraint_name);
END LOOP;
END;
/
Enable:
BEGIN
FOR i IN
( SELECT c.owner
, c.table_name
, c.constraint_name
FROM user_constraints c
JOIN user_tables t ON c.table_name = t.table_name
WHERE c.status = 'DISABLED'
ORDER BY c.constraint_type
)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP;
END;
/
Subscribe to:
Posts (Atom)
Posts
