OraFAQ Articles
Tips to install Oracle 11gr2 RAC on AIX (6.1/7.1)
AIX is like an Unix environment awesome original, same to HP-Unix, and, if you have a plan to install Oracle RAC, you need to pay attention. I note some tips in this article to help.
1. Checking Operating System Packages
# lslpp -l bos.adt.base bos.adt.lib bos.adt.libm bos.perf.libperfstat \ bos.perf.perfstat bos.perf.proctools rsct.basic.rte rsct.compat.clients.rte \ xlC.aix61.rte
If not, install on AIX source by smity. It's easy, but remember, some packaged requires your IBM's account to download.
2. Verify UDP and TCP Kernel Parameters
# /usr/sbin/no -a | fgrep ephemeral
If you expect your workload to require a high number of ephemeral ports, then update
the UDP and TCP ephemeral port range to a broader range. For example:
# /usr/sbin/no -p -o tcp_ephemeral_low=9000 -o tcp_ephemeral_high=65500 # /usr/sbin/no -p -o udp_ephemeral_low=9000 -o udp_ephemeral_high=65500
3. Checking Resource Limits:
To ensure that these resource limits are honored, confirm that the line login
session required /usr/lib/security/pam_aix is set in /etc/pam.conf.For example:
dtsession auth required /usr/lib/security/pam_aix dtlogin session required /usr/lib/security/pam_aix ftp session required /usr/lib/security/pam_aix imap session required /usr/lib/security/pam_aix login session required /usr/lib/security/pam_aix rexec session required /usr/lib/security/pam_aix rlogin session required /usr/lib/security/pam_aix rsh session required /usr/lib/security/pam_aix snapp session required /usr/lib/security/pam_aix su session required /usr/lib/security/pam_aix swrole session required /usr/lib/security/pam_aix telnet session required /usr/lib/security/pam_aix xdm session required /usr/lib/security/pam_aix OTHER session required /usr/lib/security/pam_prohibit websm_rlogin session required /usr/lib/security/pam_aix websm_su session required /usr/lib/security/pam_aix wbem session required /usr/lib/security/pam_aix
4. Tuning AIX System Environment
Confirm the aio_maxreqs value using the procedure for your release:
AIX 6.1 and 7.1:
# ioo -o aio_maxreqs aio_maxreqs = 65536
The aio is Asynchronous Input Output is an exciting parameter, I tried to control and modified it many times, but it's strongly to do from Oracle advices,
Adjust the initial value of aio_maxservers to 10 times the number of logical disks divided by the number of CPUs that are to be used concurrently but no more than 80
Oracle document refer: https://docs.oracle.com/database/121/AXDBI/app_manual.htm#AXDBI7880
5. Tuning Virtual Memory Manager
vmo -p -o minperm%=3 vmo -p -o maxperm%=90 vmo -p -o maxclient%=90 vmo -p -o lru_file_repage=0 vmo -p -o strict_maxclient=1 vmo -p -o strict_maxperm=0
Note: You must restart the system for these changes to take effect
6. Increase System block size allocation
# /usr/sbin/chdev -l sys0 -a ncargs='128'
7. Configure SSH LoginGraceTime Parameter
On AIX systems, the OpenSSH parameter LoginGraceTime by default is commented
out, and the default behavior of OpenSSH on AIX can sometimes result in timeout
errors. To avoid these errors, complete the following procedure:
7.1. Log in as root.
7.2. Using a text editor, open the OpenSSH configuration file /etc/ssh/sshd_config.
7.3. Locate the comment line #LoginGraceTime 2m.
7.4. Uncomment the line, and change the value to 0 (unlimited). For example:
LoginGraceTime 0
7.5. Save /etc/ssh/sshd_config.
7.6. Restart SSH.
8. Setting priviledge to Oracle ASM Luns
Same to Solaris, HP-Unix. Remember, when you've got failure of ASM configuration, you need to flush out the disk's slice/partition by OS command "dd". And the slice/partition/LUN allocated from storage to IBM, has got different first alphabet to other platform. The alphabet begins by "r", example:
7.1 ORC and Voting disk # chown grid:asmadmin /dev/rhdisk5 -> OCR # chmod 660 /dev/rhdisk5 # chown grid:asmadmin /dev/rhdisk6 -> Voting Disk # chmod 660 /dev/rhdisk6 7.2 Datafile, Archivelog and Backup # chown grid:asmadmin /dev/rhdisk2 # chmod 660 /dev/rhdisk2 # chown grid:asmadmin /dev/rhdisk3 # chmod 660 /dev/rhdisk3 # chown grid:asmadmin /dev/rhdisk4 # chmod 660 /dev/rhdisk4 # chown grid:asmadmin /dev/rhdisk9 # chmod 660 /dev/rhdisk9 # chown grid:asmadmin /dev/rhdisk10 # chmod 660 /dev/rhdisk10
9. Enable simultaneous access to a disk device from multiple nodes
To enable simultaneous access to a disk device from multiple nodes, you must set the appropriate Object Data Manager (ODM) attribute, depending on the type of reserve attribute used by your disks. The following section describes how to perform this task using hdisk logical names
8.1. determine the reserve setting your disks use, enter the following command,where n is the hdisk device number
# lsattr -E -l hdiskn | grep reserve_
The response is either a reserve_lock setting, or a reserve_policy setting. If the attribute is reserve_lock, then ensure that the setting is reserve_lock = no. If the attribute is reserve_policy, then ensure that the setting is reserve_policy = no_reserve.
8.2. If necessary, change the setting with the chdev command using the following syntax, where n is the hdisk device number:
chdev -l hdiskn -a [ reserve_lock=no | reserve_policy=no_reserve ]
For example:
# chdev -l hdisk5 -a reserve_lock=no # chdev -l hdisk5 -a reserve_policy=no_reserve
8.3. Enter commands similar to the following on any node to clear the PVID from each disk device that you want to use:
# /usr/sbin/chdev -l hdiskn -a pv=clear
When you are installing Oracle Clusterware, you must enter the paths to the appropriate device files when prompted for the path of the OCR and Oracle Clusterware voting disk. For example: /dev/rhdisk10
9.Configure Shell Limits
9.1. Add the following lines to the /etc/security/limits file:
default: fsize = -1 core = 2097151 cpu = -1 data = -1 rss = -1 stack = -1 nofiles = -1
9.2.Enter the following command to list the current setting for the maximum number of process allowed by the Oracle software user:
/usr/sbin/lsattr -E -l sys0 -a maxuproc
If necessary, change the maxuproc setting using the following command:
/usr/sbin/chdev -l sys0 -a maxuproc=16384
10. Configure User Process Parameters (Verify that the maximum number of processes allowed for each user is set to 2048 or greater)
Enter the following command:
# smit chgsys
Verify that the value shown for Maximum number of PROCESSES allowed for each user is greater than or equal to 2048. If necessary, edit the existing value.
When you have finished making changes, press Enter, then Esc+0 (Exit) to exit.
11. Configure Network Tuning Parameters:
To check the current values of the network tuning parameters:
# no -a | more
If the system is running in compatibility mode, then follow these steps to change the parameter values:
Enter commands similar to the following to change the value of each parameter:
# no -o parameter_name=value
For example:
# no -o udp_recvspace=655360
Add entries similar to the following to the /etc/rc.net file for each parameter that you changed in the previous step:
if [ -f /usr/sbin/no ] ; then /usr/sbin/no -o udp_sendspace=65536 /usr/sbin/no -o udp_recvspace=655360 /usr/sbin/no -o tcp_sendspace=65536 /usr/sbin/no -o tcp_recvspace=65536 /usr/sbin/no -o rfc1323=1 /usr/sbin/no -o sb_max=4194304 /usr/sbin/no -o ipqmaxlen=512 fi
For the ISNO parameter tcp_sendspace, use the following command to set it:
# ifconfig en0 tcp_sendspace 65536
By adding these lines to the /etc/rc.net file, the values persist when the system restarts.
12. Automatic SSH configuration
By default, OUI searches for SSH public keys in the directory /usr/local/etc/, and ssh-keygen binaries in /usr/local/bin. However, on AIX, SSH public keys typically are located in the path /etc/ssh, and ssh-keygen binaries are located in the path /usr/bin. To ensure that OUI can set up SSH, use the following command to create soft links:
# ln -s /etc/ssh /usr/local/etc # ln -s /usr/bin /usr/local/bin
In rare cases, Oracle Clusterware installation may fail during the "AttachHome" operation when the remote node closes the SSH connection. To avoid this problem, set the following parameter in the SSH daemon configuration file /etc/ssh/sshd_config on all cluster nodes to set the timeout wait to unlimited:
LoginGraceTime 0
13. Shell Limit
Adding these line in /etc/security/limits
default: fsize = -1 core = 2097151 cpu = -1 data = -1 rss = -1 stack = -1 nofiles = -1
14. Create groups and users
# mkgroup -'A' id='1000' adms='root' oinstall # mkgroup -'A' id='1031' adms='root' dba # mkgroup -'A' id='1032' adms='root' oper # mkgroup -'A' id='1020' adms='root' asmadmin # mkgroup -'A' id='1022' adms='root' asmoper # mkgroup -'A' id='1021' adms='root' asmdba # mkuser id='1100' pgrp='oinstall' groups='dba,asmadmin,asmoper,asmdba' home='/portalgrid/grid' grid # mkuser id='1101' pgrp='oinstall' groups='dba,oper,asmdba' home='/portaloracle/oracle' oracle
# mkdir -p /portalapp/app/11.2.0/grid # mkdir -p /portalapp/app/grid # mkdir -p /portalapp/app/oracle
# chown grid:oinstall /portalapp/app/11.2.0/grid <- GRID_HOME # chown grid:oinstall /portalapp/app/grid <- GRID_BASE (ORACLE_BASE for Grid user) # chown -R grid:oinstall /portalapp # chown oracle:oinstall /portalapp/app/oracle # chmod -R 775 /portalapp/
15. Setting the profile
15.1. Grid Profile
export TEMP=/tmp export TMP=/tmp export TMPDIR=/tmp umask 022 export ORACLE_HOSTNAME=portal1 export ORACLE_BASE=/portalapp/app/grid export ORACLE_HOME=/portalapp/app/11.2.0/grid export GRID_HOME=/portalapp/app/11.2.0/grid export CRS_HOME=/portalapp/app/11.2.0/grid export ORACLE_SID=+ASM1 export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib
16. Prevent Xserver does not display correct term
# startsrc -x
17. Create the following softlink needed for some Oracle utilites
# ln -s /usr/sbin/lsattr /etc/lsattr
To check existing capabilities, enter the following command as root; in this example,
the Grid installation user account is grid:
# /usr/bin/lsuser -a capabilities grid
To add capabilities, enter a command similar to the following:
# /usr/bin/chuser capabilities=CAP_NUMA_ATTACH,CAP_BYPASS_RAC_VMM,CAP_PROPAGATE grid
18. Remember to run the Installation fixup scripts
$ ./runcluvfy.sh stage -pre crsinst -n node -fixup -verbose
With Oracle Clusterware 11g release 2, Oracle Universal Installer (OUI) detects when the minimum requirements for an installation are not met, and creates shell scripts,
called fixup scripts, to finish incomplete system configuration steps. If OUI detects an incomplete task, then it generates fixup scripts (runfixup.sh). You can run the fixup script after you click the Fix and Check Again Button.
19. In the installation progressing, when root.sh at node 2, can the error such as "CRS appear in node 1, did not attemp to stop cluster, re-join cluster, by pass and continue installation.
- If Xterm did not occur, then do: $ export ORACLE_TERM=dtterm
- Manually export ORACLE_BASE, ORACLE_HOME when make an installation before running runInstaller.sh
- If /tmp is too small <500MB, then make a private directory point to other directory, change owner to grid, oracle user, example:
A. Grid
# cd /portallog # mkdir /portallog/tmp # chown -R grid:oinstall /portallog/tmp
B. Oracle
# cd /portal # mkdir tmp # chown -R oracle:dba /portal/tmp
C. Export
# export TMP=/portallog/tmp # export TEMPDIR=/portallog/tmp # export TMPDIR=/portallog/tmp
Hope this help.
End.
TAT
Tips to install Oracle 11gr2 RAC on AIX (6.1/7.1)
AIX is an Unix environment basically, same to HP-Unix, and, if you have a plan to install Oracle RAC, you need to pay attention. I note some tips in this article to help.
1. Checking Operating System Packages
# lslpp -l bos.adt.base bos.adt.lib bos.adt.libm bos.perf.libperfstat \ bos.perf.perfstat bos.perf.proctools rsct.basic.rte rsct.compat.clients.rte \ xlC.aix61.rte
If not, install on AIX source by smity. It's easy, but remember, some packaged requires your IBM's account to download.
2. Verify UDP and TCP Kernel Parameters
# /usr/sbin/no -a | fgrep ephemeral
If you expect your workload to require a high number of ephemeral ports, then update
the UDP and TCP ephemeral port range to a broader range. For example:
# /usr/sbin/no -p -o tcp_ephemeral_low=9000 -o tcp_ephemeral_high=65500 # /usr/sbin/no -p -o udp_ephemeral_low=9000 -o udp_ephemeral_high=65500
3. Checking Resource Limits:
To ensure that these resource limits are honored, confirm that the line login
session required /usr/lib/security/pam_aix is set in /etc/pam.conf.For example:
dtsession auth required /usr/lib/security/pam_aix dtlogin session required /usr/lib/security/pam_aix ftp session required /usr/lib/security/pam_aix imap session required /usr/lib/security/pam_aix login session required /usr/lib/security/pam_aix rexec session required /usr/lib/security/pam_aix rlogin session required /usr/lib/security/pam_aix rsh session required /usr/lib/security/pam_aix snapp session required /usr/lib/security/pam_aix su session required /usr/lib/security/pam_aix swrole session required /usr/lib/security/pam_aix telnet session required /usr/lib/security/pam_aix xdm session required /usr/lib/security/pam_aix OTHER session required /usr/lib/security/pam_prohibit websm_rlogin session required /usr/lib/security/pam_aix websm_su session required /usr/lib/security/pam_aix wbem session required /usr/lib/security/pam_aix
4. Tuning AIX System Environment
Confirm the aio_maxreqs value using the procedure for your release:
AIX 6.1 and 7.1:
# ioo -o aio_maxreqs aio_maxreqs = 65536
The aio is Asynchronous Input Output is an exciting parameter, I tried to control and modified it many times, but it's strongly to do from Oracle advices,
Adjust the initial value of aio_maxservers to 10 times the number of logical disks divided by the number of CPUs that are to be used concurrently but no more than 80
Oracle document refer: https://docs.oracle.com/database/121/AXDBI/app_manual.htm#AXDBI7880
5. Tuning Virtual Memory Manager
vmo -p -o minperm%=3 vmo -p -o maxperm%=90 vmo -p -o maxclient%=90 vmo -p -o lru_file_repage=0 vmo -p -o strict_maxclient=1 vmo -p -o strict_maxperm=0
Note: You must restart the system for these changes to take effect
6. Increase System block size allocation
# /usr/sbin/chdev -l sys0 -a ncargs='128'
7. Configure SSH LoginGraceTime Parameter
On AIX systems, the OpenSSH parameter LoginGraceTime by default is commented
out, and the default behavior of OpenSSH on AIX can sometimes result in timeout
errors. To avoid these errors, complete the following procedure:
7.1. Log in as root.
7.2. Using a text editor, open the OpenSSH configuration file /etc/ssh/sshd_config.
7.3. Locate the comment line #LoginGraceTime 2m.
7.4. Uncomment the line, and change the value to 0 (unlimited). For example:
LoginGraceTime 0
7.5. Save /etc/ssh/sshd_config.
7.6. Restart SSH.
8. Setting priviledge to Oracle ASM Luns
Same to Solaris, HP-Unix. Remember, when you've got failure of ASM configuration, you need to flush out the disk's slice/partition by OS command "dd". And the slice/partition/LUN allocated from storage to IBM, has got different first alphabet to other platform. The alphabet begins by "r", example:
7.1 ORC and Voting disk # chown grid:asmadmin /dev/rhdisk5 -> OCR # chmod 660 /dev/rhdisk5 # chown grid:asmadmin /dev/rhdisk6 -> Voting Disk # chmod 660 /dev/rhdisk6 7.2 Datafile, Archivelog and Backup # chown grid:asmadmin /dev/rhdisk2 # chmod 660 /dev/rhdisk2 # chown grid:asmadmin /dev/rhdisk3 # chmod 660 /dev/rhdisk3 # chown grid:asmadmin /dev/rhdisk4 # chmod 660 /dev/rhdisk4 # chown grid:asmadmin /dev/rhdisk9 # chmod 660 /dev/rhdisk9 # chown grid:asmadmin /dev/rhdisk10 # chmod 660 /dev/rhdisk10
9. Enable simultaneous access to a disk device from multiple nodes
To enable simultaneous access to a disk device from multiple nodes, you must set the appropriate Object Data Manager (ODM) attribute, depending on the type of reserve attribute used by your disks. The following section describes how to perform this task using hdisk logical names
8.1. determine the reserve setting your disks use, enter the following command,where n is the hdisk device number
# lsattr -E -l hdiskn | grep reserve_
The response is either a reserve_lock setting, or a reserve_policy setting. If the attribute is reserve_lock, then ensure that the setting is reserve_lock = no. If the attribute is reserve_policy, then ensure that the setting is reserve_policy = no_reserve.
8.2. If necessary, change the setting with the chdev command using the following syntax, where n is the hdisk device number:
chdev -l hdiskn -a [ reserve_lock=no | reserve_policy=no_reserve ]
For example:
# chdev -l hdisk5 -a reserve_lock=no # chdev -l hdisk5 -a reserve_policy=no_reserve
8.3. Enter commands similar to the following on any node to clear the PVID from each disk device that you want to use:
# /usr/sbin/chdev -l hdiskn -a pv=clear
When you are installing Oracle Clusterware, you must enter the paths to the appropriate device files when prompted for the path of the OCR and Oracle Clusterware voting disk. For example: /dev/rhdisk10
9.Configure Shell Limits
9.1. Add the following lines to the /etc/security/limits file:
default: fsize = -1 core = 2097151 cpu = -1 data = -1 rss = -1 stack = -1 nofiles = -1
9.2.Enter the following command to list the current setting for the maximum number of process allowed by the Oracle software user:
/usr/sbin/lsattr -E -l sys0 -a maxuproc
If necessary, change the maxuproc setting using the following command:
/usr/sbin/chdev -l sys0 -a maxuproc=16384
10. Configure User Process Parameters (Verify that the maximum number of processes allowed for each user is set to 2048 or greater)
Enter the following command:
# smit chgsys
Verify that the value shown for Maximum number of PROCESSES allowed for each user is greater than or equal to 2048. If necessary, edit the existing value.
When you have finished making changes, press Enter, then Esc+0 (Exit) to exit.
11. Configure Network Tuning Parameters:
To check the current values of the network tuning parameters:
# no -a | more
If the system is running in compatibility mode, then follow these steps to change the parameter values:
Enter commands similar to the following to change the value of each parameter:
# no -o parameter_name=value
For example:
# no -o udp_recvspace=655360
Add entries similar to the following to the /etc/rc.net file for each parameter that you changed in the previous step:
if [ -f /usr/sbin/no ] ; then /usr/sbin/no -o udp_sendspace=65536 /usr/sbin/no -o udp_recvspace=655360 /usr/sbin/no -o tcp_sendspace=65536 /usr/sbin/no -o tcp_recvspace=65536 /usr/sbin/no -o rfc1323=1 /usr/sbin/no -o sb_max=4194304 /usr/sbin/no -o ipqmaxlen=512 fi
For the ISNO parameter tcp_sendspace, use the following command to set it:
# ifconfig en0 tcp_sendspace 65536
By adding these lines to the /etc/rc.net file, the values persist when the system restarts.
12. Automatic SSH configuration
By default, OUI searches for SSH public keys in the directory /usr/local/etc/, and ssh-keygen binaries in /usr/local/bin. However, on AIX, SSH public keys typically are located in the path /etc/ssh, and ssh-keygen binaries are located in the path /usr/bin. To ensure that OUI can set up SSH, use the following command to create soft links:
# ln -s /etc/ssh /usr/local/etc # ln -s /usr/bin /usr/local/bin
In rare cases, Oracle Clusterware installation may fail during the "AttachHome" operation when the remote node closes the SSH connection. To avoid this problem, set the following parameter in the SSH daemon configuration file /etc/ssh/sshd_config on all cluster nodes to set the timeout wait to unlimited:
LoginGraceTime 0
13. Shell Limit
Adding these line in /etc/security/limits
default: fsize = -1 core = 2097151 cpu = -1 data = -1 rss = -1 stack = -1 nofiles = -1
14. Create groups and users
# mkgroup -'A' id='1000' adms='root' oinstall # mkgroup -'A' id='1031' adms='root' dba # mkgroup -'A' id='1032' adms='root' oper # mkgroup -'A' id='1020' adms='root' asmadmin # mkgroup -'A' id='1022' adms='root' asmoper # mkgroup -'A' id='1021' adms='root' asmdba # mkuser id='1100' pgrp='oinstall' groups='dba,asmadmin,asmoper,asmdba' home='/portalgrid/grid' grid # mkuser id='1101' pgrp='oinstall' groups='dba,oper,asmdba' home='/portaloracle/oracle' oracle
# mkdir -p /portalapp/app/11.2.0/grid # mkdir -p /portalapp/app/grid # mkdir -p /portalapp/app/oracle
# chown grid:oinstall /portalapp/app/11.2.0/grid <- GRID_HOME # chown grid:oinstall /portalapp/app/grid <- GRID_BASE (ORACLE_BASE for Grid user) # chown -R grid:oinstall /portalapp # chown oracle:oinstall /portalapp/app/oracle # chmod -R 775 /portalapp/
15. Setting the profile
15.1. Grid Profile
export TEMP=/tmp export TMP=/tmp export TMPDIR=/tmp umask 022 export ORACLE_HOSTNAME=portal1 export ORACLE_BASE=/portalapp/app/grid export ORACLE_HOME=/portalapp/app/11.2.0/grid export GRID_HOME=/portalapp/app/11.2.0/grid export CRS_HOME=/portalapp/app/11.2.0/grid export ORACLE_SID=+ASM1 export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib
16. Prevent Xserver does not display correct term
# startsrc -x
17. Create the following softlink needed for some Oracle utilites
# ln -s /usr/sbin/lsattr /etc/lsattr
To check existing capabilities, enter the following command as root; in this example,
the Grid installation user account is grid:
# /usr/bin/lsuser -a capabilities grid
To add capabilities, enter a command similar to the following:
# /usr/bin/chuser capabilities=CAP_NUMA_ATTACH,CAP_BYPASS_RAC_VMM,CAP_PROPAGATE grid
18. Remember to run the Installation fixup scripts
$ ./runcluvfy.sh stage -pre crsinst -n node -fixup -verbose
With Oracle Clusterware 11g release 2, Oracle Universal Installer (OUI) detects when the minimum requirements for an installation are not met, and creates shell scripts,
called fixup scripts, to finish incomplete system configuration steps. If OUI detects an incomplete task, then it generates fixup scripts (runfixup.sh). You can run the fixup script after you click the Fix and Check Again Button.
19. In the installation progressing, when root.sh at node 2, can the error such as "CRS appear in node 1, did not attemp to stop cluster, re-join cluster, by pass and continue installation.
- If Xterm did not occur, then do: $ export ORACLE_TERM=dtterm
- Manually export ORACLE_BASE, ORACLE_HOME when make an installation before running runInstaller.sh
- If /tmp is too small <500MB, then make a private directory point to other directory, change owner to grid, oracle user, example:
A. Grid
# cd /portallog # mkdir /portallog/tmp # chown -R grid:oinstall /portallog/tmp
B. Oracle
# cd /portal # mkdir tmp # chown -R oracle:dba /portal/tmp
C. Export
# export TMP=/portallog/tmp # export TEMPDIR=/portallog/tmp # export TMPDIR=/portallog/tmp
Hope this help.
End.
TAT
Recursive WITH, part III: IS_LEAF
The CONNECT BY syntax provides a useful pseudocolumn, CONNECT_BY_ISLEAF, which identifies leaf nodes in the data: it’s 1 when a row has no further children, 0 otherwise. In this post, I’ll look at emulating this pseudocolumn using recursive WITH.
Let’s continue with the example from my previous posts about hierarchical data: the skeleton from the old song “Dem Dry Bones”.
UPDATE skeleton SET connected_to_the=NULL WHERE bone='head'; SELECT * FROM skeleton; BONE CONNECTED_TO_THE ---------------------------------------- ---------------------------------------- shoulder neck back shoulder hip back thigh hip knee thigh leg knee foot heel head neck head toe foot arm shoulder wrist arm ankle leg heel ankle finger wrist a rib back b rib back c rib back
With CONNECT BY, we can use the CONNECT_BY_ISLEAF pseudocolumn to identify leaf nodes:
SELECT bone, level, ltrim(sys_connect_by_path(bone,' -> '),' -> ') AS path FROM skeleton WHERE connect_by_isleaf=1 START WITH connected_to_the IS NULL CONNECT BY prior bone=connected_to_the ORDER siblings BY 1; BONE LEVEL PATH --------- ----- ----------------------------------------------------------------------------------------------- finger 6 head -> neck -> shoulder -> arm -> wrist -> finger a rib 5 head -> neck -> shoulder -> back -> a rib b rib 5 head -> neck -> shoulder -> back -> b rib c rib 5 head -> neck -> shoulder -> back -> c rib toe 12 head -> neck -> shoulder -> back -> hip -> thigh -> knee -> leg -> ankle -> heel -> foot -> toe
This pseudocolumn takes a little more thought to replicate using recursive WITH than the LEVEL pseudocolumn and the SYS_CONNECT_BY_PATH, which, as we saw in my last post, fall naturally out of the recursion.
We can imitate CONNECT_BY_ISLEAF by searching DEPTH FIRST and using the LEAD function to peek at the next row’s the_level value. If the next row’s level is higher than the current row, then it’s a child of the current row; otherwise, it’s not a child. Since, with DEPTH FIRST, all the children of a row come out before any siblings, if the next row isn’t a child, then the current row is a leaf.
WITH skellarchy (bone, parent, the_level) AS
( SELECT bone, connected_to_the, 0 FROM skeleton
WHERE bone = 'head'
UNION ALL
SELECT s.bone, s.connected_to_the , r.the_level + 1
FROM skeleton s, skellarchy r
WHERE r.bone = s.connected_to_the
)
SEARCH DEPTH FIRST BY bone SET bone_order
CYCLE bone SET is_a_cycle TO 'Y' DEFAULT 'N'
SELECT lpad(' ',2*the_level, ' ') || bone AS bone_tree , the_level,
lead(the_level) OVER (ORDER BY bone_order) AS next_level,
CASE
WHEN the_level < lead(the_level) OVER (ORDER BY bone_order) THEN NULL
ELSE 'LEAF'
END is_leaf
FROM skellarchy
ORDER BY bone_order;
BONE_TREE THE_LEVEL NEXT_LEVEL IS_L
--------------------------------------------- ---------- ---------- ----
head 0 1
neck 1 2
shoulder 2 3
arm 3 4
wrist 4 5
finger 5 3 LEAF
back 3 4
a rib 4 4 LEAF
b rib 4 4 LEAF
c rib 4 4 LEAF
hip 4 5
thigh 5 6
knee 6 7
leg 7 8
ankle 8 9
heel 9 10
foot 10 11
toe 11 LEAF
Watch out for Cycles
The first point of caution about this solution concerns cycles. In my last post, I had created a cycle by making the ‘head’ node’s parent the ‘toe’ node. If I’d left the cycle in the data, the toe node wouldn’t be a leaf any more, but this query would falsely identify the head as a leaf:
UPDATE skeleton SET connected_to_the='toe' WHERE bone='head';
BONE_TREE THE_LEVEL NEXT_LEVEL IS_L
--------------------------------------------- ---------- ---------- ----
head 0 1
neck 1 2
shoulder 2 3
arm 3 4
wrist 4 5
finger 5 3 LEAF
back 3 4
a rib 4 4 LEAF
b rib 4 4 LEAF
c rib 4 4 LEAF
hip 4 5
thigh 5 6
knee 6 7
leg 7 8
ankle 8 9
heel 9 10
foot 10 11
toe 11 12
head 12 LEAF
19 rows selected.
This can be corrected for by adding WHERE IS_A_CYCLE=’N’ to the query.
Respect the order of evaluation…A second point of caution: if I add a WHERE clause to the query that limits the number of levels, the last line of the resultset will always be identified as a leaf.
WITH skellarchy (bone, parent, the_level) AS
( SELECT bone, connected_to_the, 0 FROM skeleton
WHERE bone = 'head'
UNION ALL
SELECT s.bone, s.connected_to_the , r.the_level + 1
FROM skeleton s, skellarchy r
WHERE r.bone = s.connected_to_the
)
SEARCH DEPTH FIRST BY bone SET bone_order
CYCLE bone SET is_a_cycle TO 'Y' DEFAULT 'N'
SELECT lpad(' ',2*the_level, ' ') || bone AS bone_tree , the_level,
lead(the_level) OVER (ORDER BY bone_order) AS next_level,
CASE
WHEN the_level < lead(the_level) OVER (ORDER BY bone_order) THEN NULL
ELSE 'LEAF'
END is_leaf
FROM skellarchy
WHERE the_level < 8
ORDER BY bone_order;
BONE_TREE THE_LEVEL NEXT_LEVEL IS_L
------------------------------------------------------------ ---------- ---------- ----
head 0 1
neck 1 2
shoulder 2 3
arm 3 4
wrist 4 5
finger 5 3 LEAF
back 3 4
a rib 4 4 LEAF
b rib 4 4 LEAF
c rib 4 4 LEAF
hip 4 5
thigh 5 6
knee 6 7
leg 7 LEAF <<<=====
The leg is falsely identified as a leaf, and NEXT_LEVEL comes out as NULL, even though the ‘leg’ row has a child row. Why is that? It’s because this solution uses the LEAD analytic function. With analytic functions, WHERE clauses are evaluated before the analytic functions.
Highlighting the relevant bits from the query:
WITH skellarchy AS ...[recursive WITH subquery]... SELECT ... LEAD(the_level) OVER (ORDER BY bone_order) AS next_level ... --analytic function FROM skellarchy WHERE the_level < 8 ... --where clause
To quote the documentation:
Analytic functions compute an aggregate value based on a group of rows…. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row…. Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed.
In the query above, “where the_level < 8" will be evaluated before LEAD(the_level). The EXPLAIN PLAN shows this very clearly:
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 76 | 8 (25)| 00:00:01 |
| 1 | WINDOW BUFFER | | 2 | 76 | 8 (25)| 00:00:01 | <<=== LEAD
|* 2 | VIEW | | 2 | 76 | 8 (25)| 00:00:01 | <<=== filter("THE_LEVEL"<8)
| 3 | UNION ALL (RECURSIVE WITH) DEPTH FIRST| | | | | |
|* 4 | TABLE ACCESS FULL | SKELETON | 1 | 24 | 2 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 1 | 49 | 5 (20)| 00:00:01 |
| 6 | RECURSIVE WITH PUMP | | | | | |
| 7 | TABLE ACCESS FULL | SKELETON | 18 | 432 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("THE_LEVEL"<8)
4 - filter("BONE"='head')
5 - access("R"."BONE"="S"."CONNECTED_TO_THE")
The WINDOW BUFFER (analytic window) is evaluated after the VIEW which filters on “THE_LEVEL”<8. So, "lead(the_level) over (order by bone_order)" will be null where the_level=7, and the 'leg' wrongly identified as a leaf node. What we actually want is for the analytic function LEAD to run over the whole resultset, and only then limit the results to show the levels 0-7. The obvious way to do this is to wrap the query in a second SELECT statement:
SELECT * FROM (
WITH skellarchy (bone, parent, the_level) AS
( SELECT bone, connected_to_the, 0 FROM skeleton
WHERE bone = 'head'
UNION ALL
SELECT s.bone, s.connected_to_the , r.the_level + 1
FROM skeleton s, skellarchy r
WHERE r.bone = s.connected_to_the
)
SEARCH DEPTH FIRST BY bone SET bone_order
CYCLE bone SET is_a_cycle TO 'Y' DEFAULT 'N'
SELECT lpad(' ',2*the_level, ' ') || bone AS bone_tree , the_level,
lead(the_level) OVER (ORDER BY bone_order) AS next_level,
CASE
WHEN the_level < lead(the_level) OVER (ORDER BY bone_order) THEN NULL
ELSE 'LEAF'
END is_leaf
FROM skellarchy
ORDER BY bone_order
) WHERE the_level < 8;
BONE_TREE THE_LEVEL NEXT_LEVEL IS_L
------------------------------------------------------------ ---------- ---------- ----
head 0 1
neck 1 2
shoulder 2 3
arm 3 4
wrist 4 5
finger 5 3 LEAF
back 3 4
a rib 4 4 LEAF
b rib 4 4 LEAF
c rib 4 4 LEAF
hip 4 5
thigh 5 6
knee 6 7
leg 7 8
Now, the analytic function in the inner query is evaluated first, before the WHERE clause in the outer query. We can see this in the EXPLAIN PLAN too, of course. Now the WINDOW BUFFER (analytic window) is evaluated before the VIEW with filter(“THE_LEVEL”<8) :
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4068 | 8 (25)| 00:00:01 |
|* 1 | VIEW | | 2 | 4068 | 8 (25)| 00:00:01 | <<=== filter("THE_LEVEL"<8)
| 2 | WINDOW BUFFER | | 2 | 76 | 8 (25)| 00:00:01 | <<=== LEAD
| 3 | VIEW | | 2 | 76 | 8 (25)| 00:00:01 |
| 4 | UNION ALL (RECURSIVE WITH) DEPTH FIRST| | | | | |
|* 5 | TABLE ACCESS FULL | SKELETON | 1 | 24 | 2 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 1 | 49 | 5 (20)| 00:00:01 |
| 7 | RECURSIVE WITH PUMP | | | | | |
| 8 | TABLE ACCESS FULL | SKELETON | 18 | 432 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("THE_LEVEL"<8)
5 - filter("BONE"='head')
6 - access("R"."BONE"="S"."CONNECTED_TO_THE")
This is one case of the general point that, as Tom Kyte explains in this Ask Tom answer,“select analytic_function from t where CONDITION” is NOT THE SAME AS “select * from (select analytic_function from t) where CONDITION”.
So, to sum up my last few posts, we can do everything that CONNECT BY can do with the 11g recursive WITH syntax. Plus, the recursive WITH syntax makes it easy to express simple recursive algorithms in SQL.
Republished with permission. Original URL: http://rdbms-insight.com/wp/?p=135Recursive WITH, part III: IS_LEAF
The CONNECT BY syntax provides a useful pseudocolumn, CONNECT_BY_ISLEAF, which identifies leaf nodes in the data: it’s 1 when a row has no further children, 0 otherwise. In this post, I’ll look at emulating this pseudocolumn using recursive WITH.
Recursive WITH, part II: Hierarchical queries
In my last post, I looked at using recursive WITH to implement simple recursive algorithms in SQL. One very common use of recursion is to traverse hierarchical data. I recently wrote a series of posts on hierarchical data, using Oracle’s CONNECT BY syntax and a fun example. In this post, I’ll be revisiting the same data using recursive WITH.
There are dozens of examples of hierarchical data, from the EMP table to the Windows Registry to binary trees, but I went with something more fun: the skeleton from the old song “Dem Dry Bones”.
Foot bone connected to the heel bone
Heel bone connected to the ankle bone
Ankle bone connected to the shin bone
Shin bone connected to the knee bone
Knee bone connected to the thigh bone
Thigh bone connected to the hip bone
Hip bone connected to the back bone
Back bone connected to the shoulder bone
Shoulder bone connected to the neck bone
Neck bone connected to the head bone
Since every bone has only one ancestor, and there is a root bone with no ancestor, this is hierarchical data and we can stick it in a table and query it.
SELECT * FROM skeleton; BONE CONNECTED_TO_THE ---------------------------------------- ---------------------------------------- shoulder neck back shoulder hip back thigh hip knee thigh leg knee foot heel head neck head toe foot arm shoulder wrist arm ankle leg heel ankle finger wrist a rib back b rib back c rib back
You can see that I added some ribs and an arm to make the skeleton more complete!
Using Oracle’s CONNECT BY syntax:
SQL> col bone FOR a10
SQL> col connected_to_the FOR a9
SQL> col level FOR 99
SQL> col bone_tree FOR a27
SQL> col path FOR a65
SELECT bone, connected_to_the, level,
lpad(' ',2*level, ' ') || bone AS bone_tree ,
ltrim(sys_connect_by_path(bone,'>'),'>') AS path
FROM skeleton
START WITH connected_to_the IS NULL
CONNECT BY prior bone=connected_to_the
ORDER siblings BY 1
BONE CONNECTED LEVEL BONE_TREE PATH
---------- --------- ----- --------------------------- -----------------------------------------------------------------
head 1 head head
neck head 2 neck head>neck
shoulder neck 3 shoulder head>neck>shoulder
arm shoulder 4 arm head>neck>shoulder>arm
wrist arm 5 wrist head>neck>shoulder>arm>wrist
finger wrist 6 finger head>neck>shoulder>arm>wrist>finger
back shoulder 4 back head>neck>shoulder>back
a rib back 5 a rib head>neck>shoulder>back>a rib
b rib back 5 b rib head>neck>shoulder>back>b rib
c rib back 5 c rib head>neck>shoulder>back>c rib
hip back 5 hip head>neck>shoulder>back>hip
thigh hip 6 thigh head>neck>shoulder>back>hip>thigh
knee thigh 7 knee head>neck>shoulder>back>hip>thigh>knee
leg knee 8 leg head>neck>shoulder>back>hip>thigh>knee>leg
ankle leg 9 ankle head>neck>shoulder>back>hip>thigh>knee>leg>ankle
heel ankle 10 heel head>neck>shoulder>back>hip>thigh>knee>leg>ankle>heel
foot heel 11 foot head>neck>shoulder>back>hip>thigh>knee>leg>ankle>heel>foot
toe foot 12 toe head>neck>shoulder>back>hip>thigh>knee>leg>ankle>heel>foot>toe
The above CONNECT BY query uses the LEVEL pseudocolumn and the SYS_CONNECT_BY_PATH function. With recursive WITH, there’s no need for these built-ins because these values fall naturally out of the recursion.
Let’s start with the basic hierarchical query rewritten in recursive WITH.
The hierarchical relationship in our table is:
Parent(row.bone) = row.connected_to_the
WITH skellarchy (bone, parent) AS ( SELECT bone, connected_to_the FROM skeleton WHERE bone = 'head' -- Start with the root UNION ALL SELECT s.bone, s.connected_to_the FROM skeleton s, skellarchy r WHERE r.bone = s.connected_to_the -- Parent(row.bone) = row.connected_to_the ) SELECT * FROM skellarchy; BONE PARENT ---------- ---------------------------------------- head neck head shoulder neck back shoulder arm shoulder hip back wrist arm a rib back b rib back c rib back thigh hip finger wrist knee thigh leg knee ankle leg heel ankle foot heel toe foot
Because we built up the SKELLARCHY table recursively, it’s easy to make an equivalent to the LEVEL pseudocolumn; it falls right out of the recursion:
WITH skellarchy (bone, parent, the_level) AS ( SELECT bone, connected_to_the, 0 FROM skeleton WHERE bone = 'head' UNION ALL SELECT s.bone, s.connected_to_the , r.the_level + 1 FROM skeleton s, skellarchy r WHERE r.bone = s.connected_to_the ) SELECT * FROM skellarchy; BONE PARENT THE_LEVEL ---------- ---------- ---------- head 0 neck head 1 shoulder neck 2 back shoulder 3 arm shoulder 3 hip back 4 wrist arm 4 a rib back 4 b rib back 4 c rib back 4 thigh hip 5 finger wrist 5 knee thigh 6 leg knee 7 ankle leg 8 heel ankle 9 foot heel 10 toe foot 11
and it’s also easy to build up a path from root to the current node like the “SYS_CONNECT_BY_PATH” function does for CONNECT BY queries:
WITH skellarchy (bone, parent, the_level, the_path) AS ( SELECT bone, connected_to_the, 0, CAST(bone AS varchar2(4000)) FROM skeleton WHERE bone = 'head' UNION ALL SELECT s.bone, s.connected_to_the , r.the_level + 1, r.the_path || '->' || s.bone FROM skeleton s, skellarchy r WHERE r.bone = s.connected_to_the ) SELECT * FROM skellarchy; BONE PARENT THE_LEVEL THE_PATH ---------- ---------- --------- -------------------------------------------------------------------------------- head 0 head neck head 1 head->neck shoulder neck 2 head->neck->shoulder back shoulder 3 head->neck->shoulder->back arm shoulder 3 head->neck->shoulder->arm hip back 4 head->neck->shoulder->back->hip wrist arm 4 head->neck->shoulder->arm->wrist a rib back 4 head->neck->shoulder->back->a rib b rib back 4 head->neck->shoulder->back->b rib c rib back 4 head->neck->shoulder->back->c rib thigh hip 5 head->neck->shoulder->back->hip->thigh finger wrist 5 head->neck->shoulder->arm->wrist->finger knee thigh 6 head->neck->shoulder->back->hip->thigh->knee leg knee 7 head->neck->shoulder->back->hip->thigh->knee->leg ankle leg 8 head->neck->shoulder->back->hip->thigh->knee->leg->ankle heel ankle 9 head->neck->shoulder->back->hip->thigh->knee->leg->ankle->heel foot heel 10 head->neck->shoulder->back->hip->thigh->knee->leg->ankle->heel->foot toe foot 11 head->neck->shoulder->back->hip->thigh->knee->leg->ankle->heel->foot->toe
and we can use our generated the_level column to make a nice display just as we used the level pseudocolumn with CONNECT BY:
WITH skellarchy (bone, parent, the_level) AS
( SELECT bone, connected_to_the, 0 FROM skeleton
WHERE bone = 'head'
UNION ALL
SELECT s.bone, s.connected_to_the , r.the_level + 1
FROM skeleton s, skellarchy r
WHERE r.bone = s.connected_to_the
)
SELECT lpad(' ',2*the_level, ' ') || bone AS bone_tree FROM skellarchy;
BONE_TREE
---------------------------
head
neck
shoulder
back
arm
hip
wrist
a rib
b rib
c rib
thigh
finger
knee
leg
ankle
heel
foot
toe
Now, the bones are coming out in a bit of a funny order for a skeleton. Instead of this:
shoulder
back
arm
hip
wrist
a rib
b rib
c rib
thigh
finger
I want to see this:
shoulder
arm
wrist
finger
back
a rib
b rib
c rib
hip
thigh
The rows are coming out in BREADTH FIRST ordering – meaning all siblings of ‘shoulder’ are printed before any children of ‘shoulder’. But I want to see them in DEPTH FIRST: going from shoulder to finger before we start on the backbone.
WITH skellarchy (bone, parent, the_level) AS
( SELECT bone, connected_to_the, 0 FROM skeleton
WHERE bone = 'head'
UNION ALL
SELECT s.bone, s.connected_to_the , r.the_level + 1
FROM skeleton s, skellarchy r
WHERE r.bone = s.connected_to_the
)
SEARCH DEPTH FIRST BY bone SET bone_order
SELECT lpad(' ',2*the_level, ' ') || bone AS bone_tree FROM skellarchy
ORDER BY bone_order;
BONE_TREE
---------------------------
head
neck
shoulder
arm
wrist
finger
back
a rib
b rib
c rib
hip
thigh
knee
leg
ankle
heel
foot
toe
And now the result looks more like a proper skeleton.
Now on to cycles. A cycle is a loop in the hierarchical data: a row is its own ancestor. To put a cycle in the example data, I made the skeleton bend over and connect the head to the toe:
UPDATE skeleton SET connected_to_the='toe' WHERE bone='head';
And now if we try to run the query:
ERROR at line 2: ORA-32044: cycle detected while executing recursive WITH query
With the CONNECT BY syntax, we can use CONNECT BY NOCYCLE to run a query even when cycles exist, and the pseudocolumn CONNECT_BY_IS_CYCLE to help detect cycles. For recursive WITH, Oracle provides a CYCLE clause, which is a bit more powerful as it allows us to name the column which is cycling.
WITH skellarchy (bone, parent, the_level) AS
( SELECT bone, connected_to_the, 0 FROM skeleton
WHERE bone = 'head'
UNION ALL
SELECT s.bone, s.connected_to_the , r.the_level + 1
FROM skeleton s, skellarchy r
WHERE r.bone = s.connected_to_the
)
SEARCH DEPTH FIRST BY bone SET bone_order
CYCLE bone SET is_a_cycle TO 'Y' DEFAULT 'N'
SELECT lpad(' ',2*the_level, ' ') || bone AS bone_tree, is_a_cycle FROM skellarchy
--where is_a_cycle='N'
ORDER BY bone_order;
BONE_TREE I
------------------------------------------------------------ -
head N
neck N
shoulder N
arm N
wrist N
finger N
back N
a rib N
b rib N
c rib N
hip N
thigh N
knee N
leg N
ankle N
heel N
foot N
toe N
head Y
The query runs until the first cycle is detected, then stops.
The CONNECT BY syntax does provide a nice pseudocolumn, CONNECT_BY_ISLEAF, which is 1 when a row has no further children, 0 otherwise. In my next post, I’ll look at emulating this pseudocolumn with recursive WITH.
Republished with permission. Original URL: http://rdbms-insight.com/wp/?p=103Recursive WITH, part II: Hierarchical queries
In my last post, I looked at using recursive WITH to implement simple recursive algorithms in SQL. One very common use of recursion is to traverse hierarchical data. I recently wrote a series of posts on hierarchical data, using Oracle’s CONNECT BY syntax and a fun example. In this post, I’ll be revisiting the same data using recursive WITH.
Recursion with recursive WITH
I recently had the opportunity to talk with Tom Kyte (!), and in the course of our conversation, he really made me face up to the fact that the SQL syntax I use every day is frozen in time: I’m not making much use of the analytic functions and other syntax that Oracle has introduced since 8i.
Here’s a brief history of these additions to Oracle SQL, from Keith Laker, Oracle’s Product Manager for Analytical SQL:
Not only do these make complex queries much, much simpler and easier, they are also much faster for the same result than non-analytic SQL, as Tom Kyte has shown repeatedly on his blog and in his books.
So, I was sold and I wanted to jump in with Recursive WITH. The WITH clause lets you define inline views to use across an entire query, and the coolest thing about this is that you can define the subquery recursively – so that the inline view calls itself.
Recursive WITH basic syntax:
WITH Tablename (col1, col2, ...) AS (SELECT A, B, C... FROM dual --anchor member UNION ALL SELECT A', B', C'... from Tablename where... --recursive member ) select ... from Tablename where ...Refactoring the Factorial
One fun thing about recursive WITH, aka recursive subquery refactoring, is the ease with which we can implement a recursive algorithm in SQL. Let’s warm up with a classic example of recursion: finding the factorial of a number. Factorial(n) = n! = 1*2*3*…*n . It’s a classic example because Factorial(n) can be defined recursively as:
Factorial(0) = 1 Factorial(n) = Factorial(n-1) * n
Here’s a first pass at implementing that directly in SQL to find the factorial of 5, using a recursive WITH subquery:
WITH Factorial (operand,total_so_far) AS
(SELECT 5 operand, 5 total_so_far FROM dual -- Using anchor member to pass in "5"
UNION ALL
SELECT operand-1, total_so_far * (operand-1) FROM Factorial
WHERE operand > 1)
SELECT * FROM Factorial;
OPERAND TOTAL_SO_F
---------- ----------
5 5
4 20
3 60
2 120
1 120
and to display just the result, we select it from Factorial:
WITH Factorial (operand,total_so_far) AS (SELECT 5 operand, 5 total_so_far FROM dual -- Find the factorial of 5 UNION ALL SELECT operand-1, total_so_far * (operand-1) FROM Factorial WHERE operand > 1) SELECT MAX(operand) || '! = ' || MAX(total_so_far) AS RESULT FROM Factorial; RESULT ----------------- 5! = 120
Ahem! I have cheated a little for simplicity here. The query doesn’t take into account that Factorial(0) = 1:
WITH Factorial (operand,total_so_far) AS
(SELECT 0 operand, 0 total_so_far FROM dual -- Find the factorial of 0
UNION ALL
SELECT operand-1, total_so_far * (operand-1) FROM Factorial
WHERE operand > 1) -- This is going to get me nowhere fast...
SELECT * FROM Factorial;
OPERAND TOTAL_SO_F
---------- ----------
0 0
To do it properly, we need to include Factorial(0) = 1 in the recursive subquery:
WITH Factorial (operand,total_so_far) AS (SELECT 0 operand, 0 total_so_far FROM dual -- Find the factorial of 0 UNION ALL SELECT operand-1, CASE -- Factorial (0) = 1 WHEN operand=0 THEN 1 ELSE (total_so_far * (operand-1)) END FROM Factorial WHERE operand >= 0) SELECT MAX(operand) || '! = ' || MAX(total_so_far) AS RESULT FROM Factorial; RESULT ------------------------------------------------------------------------------------ 0! = 1
We can also reverse direction and recursively build a table of factorials, multiplying as we go.
That’s the approach Lucas Jellema takes in his excellent blog post on factorials in SQL.
WITH Factorial (operand,output) AS
(SELECT 0 operand, 1 output FROM dual
UNION ALL
SELECT operand+1, output * (operand+1) FROM Factorial
WHERE operand < 5)
SELECT * FROM Factorial;
OPERAND OUTPUT
---------- ----------
0 1
1 1
2 2
3 6
4 24
5 120
There are two nice things about this approach: first, every row of the subquery result contains n and n! , and second, the rule that 0! = 1 is elegantly captured in the anchor member.
denrael ev’ew tahw gniylppANow let’s do something more interesting – reversing a string. Here’s some sample code in C from the CS 211 course at Cornell:
public String reverseString(String word) {
if(word == null || word.equals(""))
return word;
else
return reverseString(word.substring(1, word.length())) +
word.substring(0,1);
}
Let’s run through an example word to see how it works. For simplicity I’ll write reverseString(“word”) as r(word). Using “cat” as the word, stepping through the algorithm gives:
r(cat) = r(r(at))+c = r(r(r(t))+a+c = r(r(r(r())+t+a+c = ''+t+a+c = tac
Now to rewrite the same function in SQL. Using the same example string, “cat,” I want my recursively defined table to look like this:
in out
--------
cat
at c
t ac
tac
In C, the initial letter in the word is the 0th letter, and in SQL, it’s the 1st letter. So the C expression word.substring(1,N) corresponds to SQL expression substr(word,2,N-1) . With that in mind, it’s easy to rewrite the C algorithm in SQL:
WITH WordReverse (INPUT, output) AS
(SELECT 'CAT' INPUT, NULL output FROM dual
UNION ALL
SELECT substr(INPUT,2,LENGTH(INPUT)-1), substr(INPUT,1,1) || output
FROM wordReverse
WHERE LENGTH(INPUT) > 0
)
SELECT * FROM wordReverse;
INPUT OUTP
-------- ----
CAT
AT C
T AC
TAC
NOTE: if using 11.2.0.3 or earlier, you might get “ORA-01489: result of string concatenation is too long” when reversing anything longer than a few letters. This is due to Bug 13876895: False ora-01489 on recursive WITH clause when concatenating columns. The bug is fixed in 11.2.0.4 and 12.1.0.1, and there’s an easy workaround: Cast one of the inputs to the concatenation as a varchar2(4000).
We could make this query user-friendlier by using a sql*plus variable to hold the input string. Another approach is to add an additional subquery to the with block to “pass in” parameters. I picked this up from Lucas Jellema’s post mentioned above, and wanted to give it a try, so I’ll add it in to my WordReverse query here.
Let’s use this to reverse a word that’s really quite atrocious:
WITH params AS (SELECT 'supercalifragilisticexpialidocious' phrase FROM dual), WordReverse (inpt, outpt) AS (SELECT phrase inpt, CAST(NULL AS varchar2(4000)) outpt FROM params UNION ALL SELECT substr(inpt,2,LENGTH(inpt)-1), substr(inpt,1,1) || outpt FROM wordReverse WHERE LENGTH(inpt) > 0 ) SELECT phrase,outpt AS reversed FROM wordReverse, params WHERE LENGTH(outpt) = LENGTH(phrase) ; PHRASE REVERSED ---------------------------------- ---------------------------------------- supercalifragilisticexpialidocious suoicodilaipxecitsiligarfilacrepus
Now you might not have needed to know how to spell “supercalifragilisticexpialidocious” backwards, but one recursive requirement that does come up often is querying hierarchical data. I wrote a series of posts on hierarchical data recently, using Oracle’s CONNECT BY syntax. But recursive WITH can also be used to query hierarchical data. That’ll be the subject of my next post.
Republished with permission. Original URL: http://rdbms-insight.com/wp/?p=94Recursion with recursive WITH
I recently had the opportunity to talk with Tom Kyte (!), and in the course of our conversation, he really made me face up to the fact that the SQL syntax I use every day is frozen in time: I’m not making much use of the analytic functions and other syntax that Oracle has introduced since 8i.
SQL*Plus error logging – New feature release 11.1
One of the most important things that a developer does apart from just code development is, debugging. Isn’t it? Yes, debugging the code to fix the errors that are raised. But, in order to actually debug, we need to first capture them somewhere. As of now, any application has it’s own user defined error logging table(s).
Imagine, if the tool is rich enough to automatically capture the errors. It is very much possible now with the new SQL*PLus release 11.1
A lot of times developers complain that they do not have privilege to create tables and thus they cannot log the errors in a user defined error logging table. In such cases, it’s a really helpful feature, at least during the unit testing of the code.
I made a small demonstration in SCOTT schema using the default error log table SPERRORLOG, hope this step by step demo helps to understand easily :
NOTE : SQL*Plus error logging is set OFF by default. So, you need to “set errorlogging on” to use the SPERRORLOG table.
SP2 Error
Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> desc sperrorlog; Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(256) TIMESTAMP TIMESTAMP(6) SCRIPT VARCHAR2(1024) IDENTIFIER VARCHAR2(256) MESSAGE CLOB STATEMENT CLOB SQL> truncate table sperrorlog; Table truncated. SQL> set errorlogging on; SQL> selct * from dual; SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored. SQL> select timestamp, username, script, statement, message from sperrorlog; TIMESTAMP --------------------------------------------------------------------------- USERNAME -------------------------------------------------------------------------------- SCRIPT -------------------------------------------------------------------------------- STATEMENT -------------------------------------------------------------------------------- MESSAGE -------------------------------------------------------------------------------- 11-SEP-13 01.27.29.000000 AM SCOTT TIMESTAMP --------------------------------------------------------------------------- USERNAME -------------------------------------------------------------------------------- SCRIPT -------------------------------------------------------------------------------- STATEMENT -------------------------------------------------------------------------------- MESSAGE -------------------------------------------------------------------------------- selct * from dual; SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
ORA Error
SQL> truncate table sperrorlog;
Table truncated.
SQL> select * from dula;
select * from dula
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select timestamp, username, script, statement, message from sperrorlog;
TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
11-SEP-13 01.36.08.000000 AM
SCOTT
TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
select * from dula
ORA-00942: table or view does not exist
Like shown above, you can capture PLS errors too.
If you want to execute it through scripts, you can do it like this, and later spool the errors into a file. I kept these three lines in the sperrorlog_test.sql file -
truncate table sperrorlog;
selct * from dual;
select * from dula;
SQL> @D:\sperrorlog_test.sql;
Table truncated.
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
select * from dula
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select TIMESTAMP, SCRIPT, STATEMENT, MESSAGE from sperrorlog;
TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
11-SEP-13 01.50.17.000000 AM
D:\sperrorlog_test.sql;
SP2-0734: unknown command beginning "D:\sperror..." - rest of line ignored.
TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
11-SEP-13 01.50.27.000000 AM
D:\sperrorlog_test.sql
selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
11-SEP-13 01.50.27.000000 AM
D:\sperrorlog_test.sql
select * from dula
ORA-00942: table or view does not exist
SQL>
Check Oracle documentation on SPERRORLOG.
In addition to above, if you want to be particularly specific about each session’s error to be spooled into a file you could do this -
SQL> set errorlogging on identifier my_session_identifier
Above mentioned IDENTIFIER keyword becomes a column in SPERRORLOG table. It would get populated with the string value “my_session_identifier”. Now you just need to do this -
SQL> select timestamp, username, script, statement, message 2 from sperrorlog 3 where identifier = 'my_session_identifier';
To spool the session specific errors into a file, just do this -
SQL> spool error.log SQL> select timestamp, username, script, statement, message 2 from sperrorlog 3 where identifier = 'my_session_identifier'; SQL> spool off
SQL*Plus error logging – New feature release 11.1
One of the most important things that a developer does apart from just code development is, debugging. Isn’t it? Yes, debugging the code to fix the errors that are raised. But, in order to actually debug, we need to first capture them somewhere. As of now, any application has it’s own user defined error logging table(s).
Imagine, if the tool is rich enough to automatically capture the errors. It is very much possible now with the new SQL*PLus release 11.1
A lot of times developers complain that they do not have privilege to create tables and thus they cannot log the errors in a user defined error logging table. In such cases, it’s a really helpful feature, at least during the unit testing of the code.
I made a small demonstration in SCOTT schema using the default error log table SPERRORLOG, hope this step by step demo helps to understand easily :
NOTE : SQL*Plus error logging is set OFF by default. So, you need to “set errorlogging on” to use the SPERRORLOG table.
SP2 Error
Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> desc sperrorlog; Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME VARCHAR2(256) TIMESTAMP TIMESTAMP(6) SCRIPT VARCHAR2(1024) IDENTIFIER VARCHAR2(256) MESSAGE CLOB STATEMENT CLOB SQL> truncate table sperrorlog; Table truncated. SQL> set errorlogging on; SQL> selct * from dual; SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored. SQL> select timestamp, username, script, statement, message from sperrorlog; TIMESTAMP --------------------------------------------------------------------------- USERNAME -------------------------------------------------------------------------------- SCRIPT -------------------------------------------------------------------------------- STATEMENT -------------------------------------------------------------------------------- MESSAGE -------------------------------------------------------------------------------- 11-SEP-13 01.27.29.000000 AM SCOTT TIMESTAMP --------------------------------------------------------------------------- USERNAME -------------------------------------------------------------------------------- SCRIPT -------------------------------------------------------------------------------- STATEMENT -------------------------------------------------------------------------------- MESSAGE -------------------------------------------------------------------------------- selct * from dual; SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
ORA Error
SQL> truncate table sperrorlog;
Table truncated.
SQL> select * from dula;
select * from dula
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select timestamp, username, script, statement, message from sperrorlog;
TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
11-SEP-13 01.36.08.000000 AM
SCOTT
TIMESTAMP
---------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
select * from dula
ORA-00942: table or view does not exist
Like shown above, you can capture PLS errors too.
If you want to execute it through scripts, you can do it like this, and later spool the errors into a file. I kept these three lines in the sperrorlog_test.sql file -
truncate table sperrorlog;
selct * from dual;
select * from dula;
SQL> @D:\sperrorlog_test.sql;
Table truncated.
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
select * from dula
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select TIMESTAMP, SCRIPT, STATEMENT, MESSAGE from sperrorlog;
TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
11-SEP-13 01.50.17.000000 AM
D:\sperrorlog_test.sql;
SP2-0734: unknown command beginning "D:\sperror..." - rest of line ignored.
TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
11-SEP-13 01.50.27.000000 AM
D:\sperrorlog_test.sql
selct * from dual;
SP2-0734: unknown command beginning "selct * fr..." - rest of line ignored.
TIMESTAMP
---------------------------------------------------------------------------
SCRIPT
--------------------------------------------------------------------------------
STATEMENT
--------------------------------------------------------------------------------
MESSAGE
--------------------------------------------------------------------------------
11-SEP-13 01.50.27.000000 AM
D:\sperrorlog_test.sql
select * from dula
ORA-00942: table or view does not exist
SQL>
Check Oracle documentation on SPERRORLOG.
In addition to above, if you want to be particularly specific about each session’s error to be spooled into a file you could do this -
SQL> set errorlogging on identifier my_session_identifier
Above mentioned IDENTIFIER keyword becomes a column in SPERRORLOG table. It would get populated with the string value “my_session_identifier”. Now you just need to do this -
SQL> select timestamp, username, script, statement, message 2 from sperrorlog 3 where identifier = 'my_session_identifier';
To spool the session specific errors into a file, just do this -
SQL> spool error.log SQL> select timestamp, username, script, statement, message 2 from sperrorlog 3 where identifier = 'my_session_identifier'; SQL> spool off
Finding gaps with analytic functions
Finding gaps is classic problem in PL/SQL. The basic concept is that you have some sort of numbers (like these: 1, 2, 3, 5, 6, 8, 9, 10, 15, 20, 21, 22, 23, 25, 26), where there’s supposed to be a fixed interval between the entries, but some entries could be missing. The gaps problem involves identifying the ranges of missing values in the sequence. For these numbers, the solution will be as follows:
START_GAP END_GAP
4 4
7 7
11 14
16 19
24 24
First, run the following code, to create tab1 table:
CREATE TABLE tab1
(
col1 INTEGER
);
Then, insert a few rows:
INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (2);
INSERT INTO tab1 VALUES (3);
INSERT INTO tab1 VALUES (5);
INSERT INTO tab1 VALUES (6);
INSERT INTO tab1 VALUES (8);
INSERT INTO tab1 VALUES (9);
INSERT INTO tab1 VALUES (10);
INSERT INTO tab1 VALUES (15);
INSERT INTO tab1 VALUES (20);
INSERT INTO tab1 VALUES (21);
INSERT INTO tab1 VALUES (22);
INSERT INTO tab1 VALUES (23);
INSERT INTO tab1 VALUES (25);
INSERT INTO tab1 VALUES (26);
COMMIT;
With data, you can take care of solving the gaps problem…
One of the most efficient solutions to the gaps problem involves using analytic functions (also known as window functions)
WITH aa AS
(SELECT col1 AS cur_value, LEAD (col1) OVER (ORDER BY col1) AS next_value
FROM tab1)
SELECT cur_value + 1 AS start_gap, next_value - 1 AS end_gap
FROM aa
WHERE next_value - cur_value > 1
ORDER BY start_gap
Using the LEAD function, you can return for each current col1 value (call it cur_value) the next value in the sequence (call it next_value). Then you can filter only pairs where the difference between the two is greater than the one.
Finding gaps with analytic functions
Finding gaps is classic problem in PL/SQL. The basic concept is that you have some sort of numbers (like these: 1, 2, 3, 5, 6, 8, 9, 10, 15, 20, 21, 22, 23, 25, 26), where there’s supposed to be a fixed interval between the entries, but some entries could be missing. The gaps problem involves identifying the ranges of missing values in the sequence. For these numbers, the solution will be as follows:
START_GAP END_GAP
4 4
7 7
11 14
16 19
24 24
First, run the following code, to create tab1 table:
CREATE TABLE tab1
(
col1 INTEGER
);
Then, insert a few rows:
INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (2);
INSERT INTO tab1 VALUES (3);
INSERT INTO tab1 VALUES (5);
INSERT INTO tab1 VALUES (6);
INSERT INTO tab1 VALUES (8);
INSERT INTO tab1 VALUES (9);
INSERT INTO tab1 VALUES (10);
INSERT INTO tab1 VALUES (15);
INSERT INTO tab1 VALUES (20);
INSERT INTO tab1 VALUES (21);
INSERT INTO tab1 VALUES (22);
INSERT INTO tab1 VALUES (23);
INSERT INTO tab1 VALUES (25);
INSERT INTO tab1 VALUES (26);
COMMIT;
With data, you can take care of solving the gaps problem…
One of the most efficient solutions to the gaps problem involves using analytic functions (also known as window functions)
WITH aa AS
(SELECT col1 AS cur_value, LEAD (col1) OVER (ORDER BY col1) AS next_value
FROM tab1)
SELECT cur_value + 1 AS start_gap, next_value - 1 AS end_gap
FROM aa
WHERE next_value - cur_value > 1
ORDER BY start_gap
Using the LEAD function, you can return for each current col1 value (call it cur_value) the next value in the sequence (call it next_value). Then you can filter only pairs where the difference between the two is greater than the one.
Inverted tables: an alternative to relational structures
The inverted table format can deliver fast and flexible query capabilities, but is not widely used. ADABAS is probably the most successful implementation, but how often do you see that nowadays? Following is a description of how to implement inverted structures within a relational database. All code run on Oracle Database 12c, release 12.1.0.1.
Consider this table and a few rows, that describe the contents of my larder:
create table food(id number,capacity varchar2(10),container varchar2(10),item varchar2(10)); insert into food values(1,'large','bag','potatoes'); insert into food values(2,'small','box','carrots'); insert into food values(3,'medium','tin','peas'); insert into food values(4,'large','box','potatoes'); insert into food values(5,'small','tin','carrots'); insert into food values(6,'medium','bag','peas'); insert into food values(7,'large','tin','potatoes'); insert into food values(8,'small','bag','carrots'); insert into food values(9,'medium','box','peas');
The queries I run against the table might be "how many large boxes have I?" or "give me all the potatoes, I don't care about how they are packed". The idea is that I do not know in advance what columns I will be using in my predicate: it could be any combination. This is a common issue in a data warehouse.
So how do I index the table to satisfy any possible query? Two obvious possibilities:
First, build an index on each column, and the optimizer can perform an index_combine operation on whatever columns happen to be listed in the predicate. But that means indexing every column - and the table might have hundreds of columns. No way can I do that.
Second, build a concatenated index across all the columns: in effect, use an IOT. That will give me range scan access if any of the predicated columns are in the leading edge of the index key followed by filtering on the rest of the predicate. Or if the predicate does not include the leading column(s), I can get skip scan access and filter. But this is pretty useless, too: there will be wildly divergent performance depending on the predicate.
The answer is to invert the table:
create table inverted(colname varchar2(10),colvalue varchar2(10),id number); insert into inverted select 'capacity',capacity,id from food; insert into inverted select 'container',container,id from food; insert into inverted select 'item',item,id from food;
Now just one index on each table can satisfy all queries:
create index food_i on food(id); create index inverted_i on inverted(colname,colvalue);
To retrieve all the large boxes:
orclz> set autotrace on explain
orclz> select * from food where id in
2 (select id from inverted where colname='capacity' and colvalue='large'
3 intersect
4 select id from inverted where colname='container' and colvalue='box');
ID CAPACITY CONTAINER ITEM
---------- ---------- ---------- ----------
4 large box potatoes
Execution Plan
----------------------------------------------------------
Plan hash value: 1945359172
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | C
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 141 |
| 1 | MERGE JOIN | | 3 | 141 |
| 2 | TABLE ACCESS BY INDEX ROWID | FOOD | 9 | 306 |
| 3 | INDEX FULL SCAN | FOOD_I | 9 | |
|* 4 | SORT JOIN | | 3 | 39 |
| 5 | VIEW | VW_NSO_1 | 3 | 39 |
| 6 | INTERSECTION | | | |
| 7 | SORT UNIQUE | | 3 | 81 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED | 3 | 81 |
|* 9 | INDEX RANGE SCAN | INVERTED_I | 3 | |
| 10 | SORT UNIQUE | | 3 | 81 |
| 11 | TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED | 3 | 81 |
|* 12 | INDEX RANGE SCAN | INVERTED_I | 3 | |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ID"="ID")
filter("ID"="ID")
9 - access("COLNAME"='capacity' AND "COLVALUE"='large')
12 - access("COLNAME"='container' AND "COLVALUE"='box')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
orclz>Or all the potatoes:
orclz> select * from food where id in
2 (select id from inverted where colname='item' and colvalue='potatoes');
ID CAPACITY CONTAINER ITEM
---------- ---------- ---------- ----------
1 large bag potatoes
4 large box potatoes
7 large tin potatoes
Execution Plan
----------------------------------------------------------
Plan hash value: 762525239
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cos
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 183 |
| 1 | NESTED LOOPS | | | |
| 2 | NESTED LOOPS | | 3 | 183 |
| 3 | SORT UNIQUE | | 3 | 81 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED | 3 | 81 |
|* 5 | INDEX RANGE SCAN | INVERTED_I | 3 | |
|* 6 | INDEX RANGE SCAN | FOOD_I | 1 | |
| 7 | TABLE ACCESS BY INDEX ROWID | FOOD | 1 | 34 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("COLNAME"='item' AND "COLVALUE"='potatoes')
6 - access("ID"="ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
orclz>Of course, consideration needs to be given to handling more complex boolean expressions; maintaining the inversion is going to take resources; and a query generator has to construct the inversion code and re-write the queries. But In principle, this structure can deliver indexed access for unpredictable predicates of any number of any columns, with no separate filtering operation. Can you do that with a normalized star schema? I don't think so.
I hope this little thought experiment has stimulated the little grey cells, and made the point that relational structures are not always optimal for all problems.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com
Inverted tables: an alternative to relational structures
The inverted table format can deliver fast and flexible query capabilities, but is not widely used. ADABAS is probably the most successful implementation, but how often do you see that nowadays? Following is a description of how to implement inverted structures within a relational database. All code run on Oracle Database 12c, release 12.1.0.1.
Consider this table and a few rows, that describe the contents of my larder:
create table food(id number,capacity varchar2(10),container varchar2(10),item varchar2(10)); insert into food values(1,'large','bag','potatoes'); insert into food values(2,'small','box','carrots'); insert into food values(3,'medium','tin','peas'); insert into food values(4,'large','box','potatoes'); insert into food values(5,'small','tin','carrots'); insert into food values(6,'medium','bag','peas'); insert into food values(7,'large','tin','potatoes'); insert into food values(8,'small','bag','carrots'); insert into food values(9,'medium','box','peas');
The queries I run against the table might be "how many large boxes have I?" or "give me all the potatoes, I don't care about how they are packed". The idea is that I do not know in advance what columns I will be using in my predicate: it could be any combination. This is a common issue in a data warehouse.
So how do I index the table to satisfy any possible query? Two obvious possibilities:
First, build an index on each column, and the optimizer can perform an index_combine operation on whatever columns happen to be listed in the predicate. But that means indexing every column - and the table might have hundreds of columns. No way can I do that.
Second, build a concatenated index across all the columns: in effect, use an IOT. That will give me range scan access if any of the predicated columns are in the leading edge of the index key followed by filtering on the rest of the predicate. Or if the predicate does not include the leading column(s), I can get skip scan access and filter. But this is pretty useless, too: there will be wildly divergent performance depending on the predicate.
The answer is to invert the table:
create table inverted(colname varchar2(10),colvalue varchar2(10),id number); insert into inverted select 'capacity',capacity,id from food; insert into inverted select 'container',container,id from food; insert into inverted select 'item',item,id from food;
Now just one index on each table can satisfy all queries:
create index food_i on food(id); create index inverted_i on inverted(colname,colvalue);
To retrieve all the large boxes:
orclz> set autotrace on explain
orclz> select * from food where id in
2 (select id from inverted where colname='capacity' and colvalue='large'
3 intersect
4 select id from inverted where colname='container' and colvalue='box');
ID CAPACITY CONTAINER ITEM
---------- ---------- ---------- ----------
4 large box potatoes
Execution Plan
----------------------------------------------------------
Plan hash value: 1945359172
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | C
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 141 |
| 1 | MERGE JOIN | | 3 | 141 |
| 2 | TABLE ACCESS BY INDEX ROWID | FOOD | 9 | 306 |
| 3 | INDEX FULL SCAN | FOOD_I | 9 | |
|* 4 | SORT JOIN | | 3 | 39 |
| 5 | VIEW | VW_NSO_1 | 3 | 39 |
| 6 | INTERSECTION | | | |
| 7 | SORT UNIQUE | | 3 | 81 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED | 3 | 81 |
|* 9 | INDEX RANGE SCAN | INVERTED_I | 3 | |
| 10 | SORT UNIQUE | | 3 | 81 |
| 11 | TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED | 3 | 81 |
|* 12 | INDEX RANGE SCAN | INVERTED_I | 3 | |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ID"="ID")
filter("ID"="ID")
9 - access("COLNAME"='capacity' AND "COLVALUE"='large')
12 - access("COLNAME"='container' AND "COLVALUE"='box')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
orclz>Or all the potatoes:
orclz> select * from food where id in
2 (select id from inverted where colname='item' and colvalue='potatoes');
ID CAPACITY CONTAINER ITEM
---------- ---------- ---------- ----------
1 large bag potatoes
4 large box potatoes
7 large tin potatoes
Execution Plan
----------------------------------------------------------
Plan hash value: 762525239
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cos
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 183 |
| 1 | NESTED LOOPS | | | |
| 2 | NESTED LOOPS | | 3 | 183 |
| 3 | SORT UNIQUE | | 3 | 81 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| INVERTED | 3 | 81 |
|* 5 | INDEX RANGE SCAN | INVERTED_I | 3 | |
|* 6 | INDEX RANGE SCAN | FOOD_I | 1 | |
| 7 | TABLE ACCESS BY INDEX ROWID | FOOD | 1 | 34 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("COLNAME"='item' AND "COLVALUE"='potatoes')
6 - access("ID"="ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
orclz>Of course, consideration needs to be given to handling more complex boolean expressions; maintaining the inversion is going to take resources; and a query generator has to construct the inversion code and re-write the queries. But In principle, this structure can deliver indexed access for unpredictable predicates of any number of any columns, with no separate filtering operation. Can you do that with a normalized star schema? I don't think so.
I hope this little thought experiment has stimulated the little grey cells, and made the point that relational structures are not always optimal for all problems.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com
Three impossibilities with partitioned indexes
There are three restrictions on indexing and partitioning: a unique index cannot be local non-prefixed; a global non-prefixed index is not possible; a bitmap index cannot be global. Why these limitations? I suspect that they are there to prevent us from doing something idiotic.
This is the table used for all examples that follow:
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) )
PARTITION BY HASH (EMPNO) PARTITIONS 4;the usual EMP table, with a partitioning clause appended. It is of course a contrived example. Perhaps I am recruiting so many employees concurrently that a non-partitioned table has problems with buffer contention that can be solved only with hash partitioning.
Why can't I have a local non-prefixed unique index?
A local non-unique index is no problem, but unique is not possible:
orclz> create index enamei on emp(ename) local;
Index created.
orclz> drop index enamei;
Index dropped.
orclz> create unique index enamei on emp(ename) local;
create unique index enamei on emp(ename) local
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
You cannot get a around the problem by separating the index from the constraint (which is always good practice):
orclz> create index enamei on emp(ename) local; Index created. orclz> alter table emp add constraint euk unique (ename); alter table emp add constraint euk unique (ename) * ERROR at line 1: ORA-01408: such column list already indexed orclz>
So what is the issue? Clearly it is not a technical limitation. But if it were possible, consder the implications for performance. When inserting a row, a unique index (or a non-unique index enforcing a unique constraint) must be searched to see if the key value already exists. For my little four partition table, that would mean four index searches: one of each local index partition. Well, OK. But what if the table were range partitioned into a thousand partitions? Then every insert would have to make a thousand index lookups. This would be unbelievably slow. By restricting unique indexes to global or local prefixed, Uncle Oracle is ensuring that we cannot create such an awful situation.
Why can't I have a global non-prefixed index?
Well, why would you want one? In my example, perhaps you want a global index on deptno, partitioned by mgr. But you can't do it:
orclz> create index deptnoi on emp(deptno) global partition by hash(mgr) partitions 4;
create index deptnoi on emp(deptno) global partition by hash(mgr) partitions 4
*
ERROR at line 1:
ORA-14038: GLOBAL partitioned index must be prefixed
orclz>This index, if it were possible, might assist a query with an equality predicate on mgr and a range predicate on deptno: prune off all the non-relevant mgr partitions, then a range scan. But exactly the same effect would be achieved by using global nonpartitioned concatenated index on mgr and deptno. If the query had only deptno in the predicate, it woud have to search each partition of the putative global partitioned index, a process which would be just about identical to a skip scan of the nonpartitioned index. And of course the concatenated index could be globally partitioned - on mgr. So there you have it: a global non-prefixed index would give you nothing that is not available in other ways.
Why can't I have a global partitioned bitmap index?
This came up on the Oracle forums recently, https://forums.oracle.com/thread/2575623
Global indexes must be prefixed. Bearing that in mind, the question needs to be re-phrased: why would anyone ever want a prefixed partitioned bitmap index? Something like this:
orclz>
orclz> create bitmap index bmi on emp(deptno) global partition by hash(deptno) partitions 4;
create bitmap index bmi on emp(deptno) global partition by hash(deptno) partitions 4
*
ERROR at line 1:
ORA-25113: GLOBAL may not be used with a bitmap index
orclz>If this were possible, what would it give you? Nothing. You would not get the usual benefit of reducing contention for concurrent inserts, because of the need to lock entire blocks of a bitmap index (and therefore ranges of rows) when doing DML. Range partitioning a bitmap index would be ludicrous, because of the need to use equality predicates to get real value from bitmaps. Even with hash partitions, you would not get any benefit from partition pruning, because using equality predicates on a bitmap index in effect prunes the index already: that is what a bitmap index is for. So it seems to me that a globally partitioned bitmap index would deliver no benefit, while adding complexity and problems of index maintenance. So I suspect that, once again, Uncle Oracle is protecting us from ourselves.
Is there a technology limitation?
I am of course open to correction, but I cannot see a technology limitation that enforces any of these three impossibilities. I'm sure they are all technically possible. But Oracle has decided that, for our own good, they will never be implemented.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com
Three impossibilities with partitioned indexes
There are three restrictions on indexing and partitioning: a unique index cannot be local non-prefixed; a global non-prefixed index is not possible; a bitmap index cannot be global. Why these limitations? I suspect that they are there to prevent us from doing something idiotic.
This is the table used for all examples that follow:
CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) )
PARTITION BY HASH (EMPNO) PARTITIONS 4;the usual EMP table, with a partitioning clause appended. It is of course a contrived example. Perhaps I am recruiting so many employees concurrently that a non-partitioned table has problems with buffer contention that can be solved only with hash partitioning.
Why can't I have a local non-prefixed unique index?
A local non-unique index is no problem, but unique is not possible:
orclz> create index enamei on emp(ename) local;
Index created.
orclz> drop index enamei;
Index dropped.
orclz> create unique index enamei on emp(ename) local;
create unique index enamei on emp(ename) local
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
You cannot get a around the problem by separating the index from the constraint (which is always good practice):
orclz> create index enamei on emp(ename) local; Index created. orclz> alter table emp add constraint euk unique (ename); alter table emp add constraint euk unique (ename) * ERROR at line 1: ORA-01408: such column list already indexed orclz>
So what is the issue? Clearly it is not a technical limitation. But if it were possible, consder the implications for performance. When inserting a row, a unique index (or a non-unique index enforcing a unique constraint) must be searched to see if the key value already exists. For my little four partition table, that would mean four index searches: one of each local index partition. Well, OK. But what if the table were range partitioned into a thousand partitions? Then every insert would have to make a thousand index lookups. This would be unbelievably slow. By restricting unique indexes to global or local prefixed, Uncle Oracle is ensuring that we cannot create such an awful situation.
Why can't I have a global non-prefixed index?
Well, why would you want one? In my example, perhaps you want a global index on deptno, partitioned by mgr. But you can't do it:
orclz> create index deptnoi on emp(deptno) global partition by hash(mgr) partitions 4;
create index deptnoi on emp(deptno) global partition by hash(mgr) partitions 4
*
ERROR at line 1:
ORA-14038: GLOBAL partitioned index must be prefixed
orclz>This index, if it were possible, might assist a query with an equality predicate on mgr and a range predicate on deptno: prune off all the non-relevant mgr partitions, then a range scan. But exactly the same effect would be achieved by using global nonpartitioned concatenated index on mgr and deptno. If the query had only deptno in the predicate, it woud have to search each partition of the putative global partitioned index, a process which would be just about identical to a skip scan of the nonpartitioned index. And of course the concatenated index could be globally partitioned - on mgr. So there you have it: a global non-prefixed index would give you nothing that is not available in other ways.
Why can't I have a global partitioned bitmap index?
This came up on the Oracle forums recently, https://forums.oracle.com/thread/2575623
Global indexes must be prefixed. Bearing that in mind, the question needs to be re-phrased: why would anyone ever want a prefixed partitioned bitmap index? Something like this:
orclz>
orclz> create bitmap index bmi on emp(deptno) global partition by hash(deptno) partitions 4;
create bitmap index bmi on emp(deptno) global partition by hash(deptno) partitions 4
*
ERROR at line 1:
ORA-25113: GLOBAL may not be used with a bitmap index
orclz>If this were possible, what would it give you? Nothing. You would not get the usual benefit of reducing contention for concurrent inserts, because of the need to lock entire blocks of a bitmap index (and therefore ranges of rows) when doing DML. Range partitioning a bitmap index would be ludicrous, because of the need to use equality predicates to get real value from bitmaps. Even with hash partitions, you would not get any benefit from partition pruning, because using equality predicates on a bitmap index in effect prunes the index already: that is what a bitmap index is for. So it seems to me that a globally partitioned bitmap index would deliver no benefit, while adding complexity and problems of index maintenance. So I suspect that, once again, Uncle Oracle is protecting us from ourselves.
Is there a technology limitation?
I am of course open to correction, but I cannot see a technology limitation that enforces any of these three impossibilities. I'm sure they are all technically possible. But Oracle has decided that, for our own good, they will never be implemented.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com
Are older releases of the database really unsupported?
I see posts on Oracle related forums about various releases (anything that isn't 11.x or 12.x) being "unsupported". This is wrong. Of course you should upgrade any 9i or 10g databases, but you don't have to.
Oracle Corporation's lifetime support policy is documented here,
Lifetime Support Policy
take a look, and you'll see that release 10.2 was in premier support until end July 2010 when it went into extended support. At end July 2013, it goes into sustaining support. Sustaining support will continue indefinitely. Even release 8.1.7 will have sustaining support indefinitely.
So what is sustaining support? That is documented here,
Lifetime support benefits
To summarize, extended support gives you everything you are likely to need. What you do not get is certification against new Oracle products or new third party products (principally, operating systems). But does that matter? I don't think so. For example, release 11.2.0.3 (still in premier support) is not certified against Windows 8, but it works fine.
Sustaining support has a more significant problem: no more patches. Not even patches for security holes, or changes in regulatory requirements. The security patch issue may of course be serious, but regulatory issues are unlikely to matter (this is a database, not a tax management system.) Think about it: 10g has been around for many years. It is pretty well de-bugged by now. If you hit a problem with no work around, you are pretty unlucky. Sustaining support gives you access to technical support, available patches, software, and documentation. That is all most sites will ever need.
Right now, I am working on a 9.2.0.8 database. It cannot be upgraded because the application software is written by a company that does not permit a database upgrade. Why not? Well, the reason may be commercial: they have a replacement product that is supported on newer databases. But that is nothing to do with me. The database works, the software works. Making it work better is a challenge - but that is what a DBA is paid to do. Don't just write it off as "unsupported".
Of course I am not suggesting that users should not upgrade to current releases - but upgrades are a huge project, and can have major implications. Running out dated software is silly, unless you have an irrefutable reason for so doing. The lack of security patches make you vulnerable to data loss. The lack of regulatory patches may make it illegal. The lack of newer facilities will be restricting the utility of the system. You may be losing money by not taking of advantage of changes of newer technology that can better exploit your hardware.
If anyone is looking for consulting support to upgrade their database - my boss will be happy to give you a quote. But I won't refuse to support you in the meantime.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com
Are older releases of the database really unsupported?
I see posts on Oracle related forums about various releases (anything that isn't 11.x or 12.x) being "unsupported". This is wrong. Of course you should upgrade any 9i or 10g databases, but you don't have to.
Oracle Corporation's lifetime support policy is documented here,
Lifetime Support Policy
take a look, and you'll see that release 10.2 was in premier support until end July 2010 when it went into extended support. At end July 2013, it goes into sustaining support. Sustaining support will continue indefinitely. Even release 8.1.7 will have sustaining support indefinitely.
So what is sustaining support? That is documented here,
Lifetime support benefits
To summarize, extended support gives you everything you are likely to need. What you do not get is certification against new Oracle products or new third party products (principally, operating systems). But does that matter? I don't think so. For example, release 11.2.0.3 (still in premier support) is not certified against Windows 8, but it works fine.
Sustaining support has a more significant problem: no more patches. Not even patches for security holes, or changes in regulatory requirements. The security patch issue may of course be serious, but regulatory issues are unlikely to matter (this is a database, not a tax management system.) Think about it: 10g has been around for many years. It is pretty well de-bugged by now. If you hit a problem with no work around, you are pretty unlucky. Sustaining support gives you access to technical support, available patches, software, and documentation. That is all most sites will ever need.
Right now, I am working on a 9.2.0.8 database. It cannot be upgraded because the application software is written by a company that does not permit a database upgrade. Why not? Well, the reason may be commercial: they have a replacement product that is supported on newer databases. But that is nothing to do with me. The database works, the software works. Making it work better is a challenge - but that is what a DBA is paid to do. Don't just write it off as "unsupported".
Of course I am not suggesting that users should not upgrade to current releases - but upgrades are a huge project, and can have major implications. Running out dated software is silly, unless you have an irrefutable reason for so doing. The lack of security patches make you vulnerable to data loss. The lack of regulatory patches may make it illegal. The lack of newer facilities will be restricting the utility of the system. You may be losing money by not taking of advantage of changes of newer technology that can better exploit your hardware.
If anyone is looking for consulting support to upgrade their database - my boss will be happy to give you a quote. But I won't refuse to support you in the meantime.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com
12C: IN DATABASE ARCHIVING
In this post, I will demonstrate a new feature introduced in 12c : In database archiving. It enables you to archive rows within a table by marking them as invisible. This is accomplshed by means of a hidden column ORA_ARCHIVE_STATE. These invisible rows are not visible to the queries but if needed, can be viewed , by setting a session parameter ROW ARCHIVAL VISIBILITY.
Overview:
-- Create test user uilm, tablespace ilmtbs
-- Connect as user uilm
-- create and populate test table (5 rows) ilmtab with row archival clause
-- Note that the table has an additional column ORA_ARCHIVE_STATE automatically created and has the default value of 0 (indicates that row is active)
-- Note that this column is not visible when we describe the table or simply issue select * from ...
-- We need to access data dictionary to view the column
-- Make two rows in the table inactive by setting ORA_ARCHIVE_STATE column to a non zero value.
-- Check that inactive rows are not visible to query
-- Set the parameter ROW ARCHIVAL VISIBILITY = all to see inactive rows also
-- Set the parameter ROW ARCHIVAL VISIBILITY = active to hide inactive rows
-- Issue an insert into ... select * and check that only 3 visible rows are inserted
-- Set the parameter ROW ARCHIVAL VISIBILITY = all to see inactive rows also
-- Issue an insert into ... select * and check that all the rows are inserted but ORA_ARCHIVE_STATE is not propagated in inserted rows
-- Disable row archiving in the table and check that column ORA_ARCHIVE_STATE is automatically dropped
-- drop tablespace ilmtbs and user uilm
Implementation :
-- Create test user, tablespace and test table
SQL> conn sys/oracle@em12c:1523/pdb1 as sysdba
sho con_name
CON_NAME
------------------------------
PDB1
SQL> set sqlprompt PDB1>
PDB1>create tablespace ilmtbs datafile '/u02/app/oracle/oradata/cdb1/pdb1/ilmtbs01.dbf' size 1m;
grant connect, resource, dba to uilm identified by oracle;
alter user uilm default tablespace ilmtbs;
conn uilm/oracle@em12c:1523/pdb1
sho con_name
CON_NAME
------------------------------
PDB1
-- create table with "row archival clause"
PDB1>drop table ilmtab purge;
create table ilmtab (id number, txt char(15)) row archival;
insert into ilmtab values (1, 'one');
insert into ilmtab values (2, 'two');
insert into ilmtab values (3, 'three');
insert into ilmtab values (4, 'four');
insert into ilmtab values (5, 'five');
commit;
-- Note that the table has an additional column ORA_ARCHIVE_STATE automatically created and has the default value of 0 (indicates that row is active)
PDB1>col ora_archive_state for a20
select id, txt, ora_archive_state from ilmtab;
ID TXT ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one 0
2 two 0
3 three 0
4 four 0
5 five 0
-- Note that this column is not visible when we describe the table or simply issue select * from ...
PDB1>desc ilmtab
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
TXT CHAR(15)
PDB1>select * from ilmtab;
ID TXT
---------- ---------------
1 one
2 two
3 three
4 four
5 five
-- Since the column is invisible, let me try and make it visible
-- Note that Since the column is maintained by oracle itself, user can't modify its attributes
PDB1>alter table ilmtab modify (ora_archive_state visible);
alter table ilmtab modify (ora_archive_state visible)
*
ERROR at line 1:
ORA-38398: DDL not allowed on the system ILM column
-- We need to access data dictionary to view the column
-- Note that this column is shown as hidden and has not been generated by user
PDB1>col hidden for a7
col USER_GENERATED for 20
col USER_GENERATED for a20
select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED
from user_tab_cols where table_name='ILMTAB';
TABLE_NAME COLUMN_NAME HID USER_GENERATED
----------- -------------------- --- --------------------
ILMTAB ORA_ARCHIVE_STATE YES NO
ILMTAB ID NO YES
ILMTAB TXT NO YES
-- We can make selected rows in the table inactive by setting ORA_ARCHIVE_STATE column to a non zero value.
This can be accomplished using update table... set ORA_ACRHIVE_STATE =
. <non-zero value>
. dbms_ilm.archivestatename(1)
-- Let's update row with id =1 with ORA_ARCHIVE_STATE=2
and update row with id =2 with dbms_ilm.archivestatename(2)
PDB1>update ilmtab set ora_archive_state=2 where id=1;
update ilmtab set ora_archive_state= dbms_ilm.archivestatename(2) where id=2;
-- Let's check whether updates have been successful and hidden rows are not visible
PDB1>select id, txt, ORA_ARCHIVE_STATE from ilmtab;
ID TXT ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three 0
4 four 0
5 five 0
-- The updated rows are not visible!!
-- Quite logical since we have made the rows active and by default only active rows are visible
-- To see inactive rows also, we need to set the parameter ROW ARCHIVAL VISIBILITY = all at session level
-- Note that the column ORA_ARCHIVE_STATE has been set to 1 for id =2 although we had set it to 2 using
dbms_ilm.archivestatename(2)
PDB1>alter session set ROW ARCHIVAL VISIBILITY = all;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;
ID TXT ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one 2
2 two 1
3 three 0
4 four 0
5 five 0
-- Note that the column ORA_ARCHIVE_STATE has been set to 1 for id =2 although we had set it to 2 using dbms_ilm.archivestatename(2)
-- Let's find out why
-- Note that The function dbms_ilm.archivestatename(n) returns only two values 0 for n=0 and 1 for n <> 0
PDB1>col state0 for a8
col state1 for a8
col state2 for a8
col state3 for a8
select dbms_ilm.archivestatename(0) state0 ,dbms_ilm.archivestatename(1) state1,
dbms_ilm.archivestatename(2) state2,dbms_ilm.archivestatename(3) state3 from dual;
STATE0 STATE1 STATE2 STATE3
-------- -------- -------- --------
0 1 1 1
-- In order to make the inactive rows (id=1,2) hidden again, we need to set the parameter ROW ARCHIVAL VISIBILITY = Active
PDB1>alter session set row archival visibility = active;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;
ID TXT ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three 0
4 four 0
5 five 0
-- Let's issue an insert into ... select *
-- Note that only 3 new rows are visible
PDB1>insert into ilmtab select * from ilmtab;
select id, txt, ora_archive_state from ilmtab;
ID TXT ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three 0
4 four 0
5 five 0
3 three 0
4 four 0
5 five 0
6 rows selected.
-- I want to check if hidden rows were also inserted
-- Let's check by making hidden rows visible again
-- Note that only visible rows(id=3,4,5) were inserted
PDB1>alter session set row archival visibility=all;
select id, txt, ora_archive_state from ilmtab;
ID TXT ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one 2
2 two 1
3 three 0
4 four 0
5 five 0
3 three 0
4 four 0
5 five 0
8 rows selected.
-- Let's set row archival visibility = all and then again insert rows from ilmtab
-- Note that all the 8 rows are inserted but ORA_ARCHIVE_STATE ha not been copied ORA_ARCHIVE_STATE <> 0 in only 2 records (id = 1,2) even now.
PDB1>alter session set row archival visibility=all;
insert into ilmtab select * from ilmtab;
select id, txt, ora_archive_state from ilmtab order by id;
ID TXT ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one 0
1 one 2
2 two 0
2 two 1
3 three 0
3 three 0
3 three 0
3 three 0
4 four 0
4 four 0
4 four 0
4 four 0
5 five 0
5 five 0
5 five 0
5 five 0
16 rows selected.
-- Disable row level archiving for the table
-- Note that as soon as row archiving is disabled, pseudo column ora_archive_state is dropped automatically
PDB1>alter table ilmtab no row archival;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;
ERROR at line 1:
ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier
PDB1>col hidden for a7
col USER_GENERATED for 20
col USER_GENERATED for a20
select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED
from user_tab_cols where table_name='ILMTAB';
TABLE_NAME COLUMN_NAME HID USER_GENERATED
----------- -------------------- --- --------------------
ILMTAB ID NO YES
ILMTAB TXT NO YES
Note : Had we created this table using sys, we could not have disabled row archiving .
-- cleanup --
PDB1>conn sys/oracle@em12c:1523/pdb1 as sysdba
drop tablespace ilmtbs including contents and datafiles;
drop user uilm cascade;
References:
http://docs.oracle.com/cd/E16655_01/server.121/e17613/part_lifecycle.htm#VLDBG14154
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
12C: IN DATABASE ARCHIVING
In this post, I will demonstrate a new feature introduced in 12c : In database archiving. It enables you to archive rows within a table by marking them as invisible. This is accomplshed by means of a hidden column ORA_ARCHIVE_STATE. These invisible rows are not visible to the queries but if needed, can be viewed , by setting a session parameter ROW ARCHIVAL VISIBILITY.
Overview:
-- Create test user uilm, tablespace ilmtbs
-- Connect as user uilm
-- create and populate test table (5 rows) ilmtab with row archival clause
-- Note that the table has an additional column ORA_ARCHIVE_STATE automatically created and has the default value of 0 (indicates that row is active)
-- Note that this column is not visible when we describe the table or simply issue select * from ...
-- We need to access data dictionary to view the column
-- Make two rows in the table inactive by setting ORA_ARCHIVE_STATE column to a non zero value.
-- Check that inactive rows are not visible to query
-- Set the parameter ROW ARCHIVAL VISIBILITY = all to see inactive rows also
-- Set the parameter ROW ARCHIVAL VISIBILITY = active to hide inactive rows
-- Issue an insert into ... select * and check that only 3 visible rows are inserted
-- Set the parameter ROW ARCHIVAL VISIBILITY = all to see inactive rows also
-- Issue an insert into ... select * and check that all the rows are inserted but ORA_ARCHIVE_STATE is not propagated in inserted rows
-- Disable row archiving in the table and check that column ORA_ARCHIVE_STATE is automatically dropped
-- drop tablespace ilmtbs and user uilm
Implementation :
-- Create test user, tablespace and test table
SQL> conn sys/oracle@em12c:1523/pdb1 as sysdba
sho con_name
CON_NAME
------------------------------
PDB1
SQL> set sqlprompt PDB1>
PDB1>create tablespace ilmtbs datafile '/u02/app/oracle/oradata/cdb1/pdb1/ilmtbs01.dbf' size 1m;
grant connect, resource, dba to uilm identified by oracle;
alter user uilm default tablespace ilmtbs;
conn uilm/oracle@em12c:1523/pdb1
sho con_name
CON_NAME
------------------------------
PDB1
-- create table with "row archival clause"
PDB1>drop table ilmtab purge;
create table ilmtab (id number, txt char(15)) row archival;
insert into ilmtab values (1, 'one');
insert into ilmtab values (2, 'two');
insert into ilmtab values (3, 'three');
insert into ilmtab values (4, 'four');
insert into ilmtab values (5, 'five');
commit;
-- Note that the table has an additional column ORA_ARCHIVE_STATE automatically created and has the default value of 0 (indicates that row is active)
PDB1>col ora_archive_state for a20
select id, txt, ora_archive_state from ilmtab;
ID TXT ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one 0
2 two 0
3 three 0
4 four 0
5 five 0
-- Note that this column is not visible when we describe the table or simply issue select * from ...
PDB1>desc ilmtab
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
TXT CHAR(15)
PDB1>select * from ilmtab;
ID TXT
---------- ---------------
1 one
2 two
3 three
4 four
5 five
-- Since the column is invisible, let me try and make it visible
-- Note that Since the column is maintained by oracle itself, user can't modify its attributes
PDB1>alter table ilmtab modify (ora_archive_state visible);
alter table ilmtab modify (ora_archive_state visible)
*
ERROR at line 1:
ORA-38398: DDL not allowed on the system ILM column
-- We need to access data dictionary to view the column
-- Note that this column is shown as hidden and has not been generated by user
PDB1>col hidden for a7
col USER_GENERATED for 20
col USER_GENERATED for a20
select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED
from user_tab_cols where table_name='ILMTAB';
TABLE_NAME COLUMN_NAME HID USER_GENERATED
----------- -------------------- --- --------------------
ILMTAB ORA_ARCHIVE_STATE YES NO
ILMTAB ID NO YES
ILMTAB TXT NO YES
-- We can make selected rows in the table inactive by setting ORA_ARCHIVE_STATE column to a non zero value.
This can be accomplished using update table... set ORA_ACRHIVE_STATE =
. <non-zero value>
. dbms_ilm.archivestatename(1)
-- Let's update row with id =1 with ORA_ARCHIVE_STATE=2
and update row with id =2 with dbms_ilm.archivestatename(2)
PDB1>update ilmtab set ora_archive_state=2 where id=1;
update ilmtab set ora_archive_state= dbms_ilm.archivestatename(2) where id=2;
-- Let's check whether updates have been successful and hidden rows are not visible
PDB1>select id, txt, ORA_ARCHIVE_STATE from ilmtab;
ID TXT ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three 0
4 four 0
5 five 0
-- The updated rows are not visible!!
-- Quite logical since we have made the rows active and by default only active rows are visible
-- To see inactive rows also, we need to set the parameter ROW ARCHIVAL VISIBILITY = all at session level
-- Note that the column ORA_ARCHIVE_STATE has been set to 1 for id =2 although we had set it to 2 using
dbms_ilm.archivestatename(2)
PDB1>alter session set ROW ARCHIVAL VISIBILITY = all;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;
ID TXT ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one 2
2 two 1
3 three 0
4 four 0
5 five 0
-- Note that the column ORA_ARCHIVE_STATE has been set to 1 for id =2 although we had set it to 2 using dbms_ilm.archivestatename(2)
-- Let's find out why
-- Note that The function dbms_ilm.archivestatename(n) returns only two values 0 for n=0 and 1 for n <> 0
PDB1>col state0 for a8
col state1 for a8
col state2 for a8
col state3 for a8
select dbms_ilm.archivestatename(0) state0 ,dbms_ilm.archivestatename(1) state1,
dbms_ilm.archivestatename(2) state2,dbms_ilm.archivestatename(3) state3 from dual;
STATE0 STATE1 STATE2 STATE3
-------- -------- -------- --------
0 1 1 1
-- In order to make the inactive rows (id=1,2) hidden again, we need to set the parameter ROW ARCHIVAL VISIBILITY = Active
PDB1>alter session set row archival visibility = active;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;
ID TXT ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three 0
4 four 0
5 five 0
-- Let's issue an insert into ... select *
-- Note that only 3 new rows are visible
PDB1>insert into ilmtab select * from ilmtab;
select id, txt, ora_archive_state from ilmtab;
ID TXT ORA_ARCHIVE_STATE
---------- --------------- --------------------
3 three 0
4 four 0
5 five 0
3 three 0
4 four 0
5 five 0
6 rows selected.
-- I want to check if hidden rows were also inserted
-- Let's check by making hidden rows visible again
-- Note that only visible rows(id=3,4,5) were inserted
PDB1>alter session set row archival visibility=all;
select id, txt, ora_archive_state from ilmtab;
ID TXT ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one 2
2 two 1
3 three 0
4 four 0
5 five 0
3 three 0
4 four 0
5 five 0
8 rows selected.
-- Let's set row archival visibility = all and then again insert rows from ilmtab
-- Note that all the 8 rows are inserted but ORA_ARCHIVE_STATE ha not been copied ORA_ARCHIVE_STATE <> 0 in only 2 records (id = 1,2) even now.
PDB1>alter session set row archival visibility=all;
insert into ilmtab select * from ilmtab;
select id, txt, ora_archive_state from ilmtab order by id;
ID TXT ORA_ARCHIVE_STATE
---------- --------------- --------------------
1 one 0
1 one 2
2 two 0
2 two 1
3 three 0
3 three 0
3 three 0
3 three 0
4 four 0
4 four 0
4 four 0
4 four 0
5 five 0
5 five 0
5 five 0
5 five 0
16 rows selected.
-- Disable row level archiving for the table
-- Note that as soon as row archiving is disabled, pseudo column ora_archive_state is dropped automatically
PDB1>alter table ilmtab no row archival;
select id, txt, ORA_ARCHIVE_STATE from ilmtab;
ERROR at line 1:
ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier
PDB1>col hidden for a7
col USER_GENERATED for 20
col USER_GENERATED for a20
select TABLE_NAME, COLUMN_NAME, HIDDEN_COLUMN, USER_GENERATED
from user_tab_cols where table_name='ILMTAB';
TABLE_NAME COLUMN_NAME HID USER_GENERATED
----------- -------------------- --- --------------------
ILMTAB ID NO YES
ILMTAB TXT NO YES
Note : Had we created this table using sys, we could not have disabled row archiving .
-- cleanup --
PDB1>conn sys/oracle@em12c:1523/pdb1 as sysdba
drop tablespace ilmtbs including contents and datafiles;
drop user uilm cascade;
References:
http://docs.oracle.com/cd/E16655_01/server.121/e17613/part_lifecycle.htm#VLDBG14154
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------