With all of the great discussions spawned by the “greatest MOOC conference in the history of MOOCs” (MRI13), it seems a good time to share a segment of a keynote presentation I gave last year on MOOC history. This presentation was at the American Association of the Colleges of Nursing (AACN) conference in April 2013. For context, I had just shared how online education was no longer an issue kept in the corner, away from mainstream higher ed, but was now affecting the traditional campus discussions.
There is a relevant graphic that was shown during this segment of the talk, showing the MOOC timeline (updated version shown below).
How not to write subqueries:
AND sal.ticket_airline || sal.ticket_number NOT IN ( SELECT sub.dsd_airline || sub.dsd_ticket_number FROM ... )
If there had been any opportunity for the optimizer to do something clever with the NOT IN, you’ve just made sure it can’t happen. On top of that you may find that you don’t get the right results – consider the following cut-n-paste:
SQL> select user from dual where 1 || 23 = 12 || 3; USER ------------------------------ TEST_USER 1 row selected.
Sometimes people simply forget that you can have multiple columns in subqueries (or in “IN Lists”) – so it’s perfectly valid to write the subquery as:
AND (sal.ticket_airline, sal.ticket_number) NOT IN ( SELECT sub.dsd_airline, sub.dsd_ticket_number FROM ... )
It’s quite likely that Oracle will actually turn this into a NOT EXISTS, or ANTI-JOIN, of course. But if it doesn’t do something nice you could try doing a manual rewrite – provided it is actually logically equivalent:
AND not exists ( select null from .... where sub.dsd_airline = sal.ticket_airline and sub.dsd_ticket_number = sal.ticket_number )
Remember: NOT IN may not translate to NOT EXISTS – see also this.
- This release is largely focused on providing new technology stack components, new configuration options, and new system management tools.
- Supports for Online Patching.
- Use of Oracle WebLogic Server instead of AutoConfig for many (but not all) of AutoConfig's previous functions.
- Use of Oracle WebLogic Server instead of Oracle Containers for Java (OC4J).
- Integration between Oracle Enterprise Manager 11g Fusion Middleware Control and Oracle Applications Manager to monitor EBS environments with Oracle WebLogic Server technology.
- Enhanced Workflow Worklist notification and management features.
Configuration Management Tools
- Fusion Middleware Control
- This tool provides a high-level view of Oracle WebLogic Server (WLS)
- It is used to configure Oracle HTTP Server
- Fusion Middleware Control also includes links to Oracle Application Manager and Oracle WebLogic Server Admin Console.
- WebLogic Server Administration Console
- Manage Oracle WebLogic Server settings and managed servers.
- Oracle Application Manager and AutoConfig
- Handles Oracle E-Business Suite settings and Database Settings
In Oracle E-Business Suite Release 12.2, OC4J has been replaced with Oracle WebLogic Server.This has resulted in a reduced role for AutoConfig in the configuration of the Oracle HTTP Server and the oacore, oafm, forms and forms-c4ws services.
Configuration Management Changes in Release 12.2
Configuration Activity - Oracle E-Business Suite Database, Concurrent Processing, Oracle Developer 10g, profile options, and other Oracle E-Business Suite components.
Prior to Release 12.2
- Oracle Applications Manager.
- Oracle Applications Manager.
Configuration Activity - Changes to HTTP Configuration.
Prior to Release 12.2
- All HTTP configuration was managed via AutoConfig templates.
- Configuration changes were done by editing the respective context variables and subsequently running AutoConfig.
- Most HTTP configuration is managed via native Oracle WebLogic Server tools, Fusion Middleware Control, or manually editing of the configuration files.
- Only a limited set of HTTP configuration files are maintained via AutoConfig.
Configuration Activity - Changes to configuration of oacore, oafm, forms and forms-c4ws services.
Prior to Release 12.2
- All configuration settings for the oacore, oafm, forms and forms-c4ws services were managed via AutoConfig templates.
- Configuration changes were accomplished by editing context variables and running AutoConfig.
- Properties for the oacore, oafm, forms and forms-c4ws services, including the classpath and JVM arguments, need to be updated through native WebLogic tools such as WebLogic Administration Console.
- The context variable values are used only to set the initial values during managed server creation.
Configuration Activity - Managing JVM instances of the oacore, oafm, forms and forms-c4ws services.
Prior to Release 12.2
- The number of instances of a service was controlled via Oracle Process Manager (OPMN).
- This number could be modified by editing the nprocs context variable, running AutoConfig, then stopping and restarting the services.
- Each JVM instance of a service corresponds to a managed server of that service type.
- The number of instances needs to be controlled by explicitly creating or deleting managed servers for the service.
We are going to start a reseller program for PFCLScan and we have started the plannng and recruitment process for this program. I have just posted a short blog on the PFCLScan website titled " PFCLScan Reseller Program ". If....[Read More]
Posted by Pete On 29/10/13 At 01:05 PM
We released version 1.3 of PFCLScan our enterprise database security scanner for Oracle a week ago. I have just posted a blog entry on the PFCLScan product site blog that describes some of the highlights of the over 220 new....[Read More]
Posted by Pete On 18/10/13 At 02:36 PM
We have just updated PFCLScan our companies database security scanner for Oracle databases to version 1.2 and added some new features and some new contents and more. We are working to release another service update also in the next couple....[Read More]
Posted by Pete On 04/09/13 At 02:45 PM
It has been a few weeks since my last blog post but don't worry I am still interested to blog about Oracle 12c database security and indeed have nearly 700 pages of notes in MS Word related to 12c security....[Read More]
Posted by Pete On 28/08/13 At 05:04 PM
There has been some big new security items added to 12cR1 such as SHA2 in DBMS_CRYPTO, code based security in PL/SQL, Data Redaction, unified audit or even privilege analysis but also as I hinted in some previous blogs there are....[Read More]
Posted by Pete On 31/07/13 At 11:11 AM
The main new feature of Oracle 12cR1 has to be the multitennant architecture that allows tennant databases to be added or plugged into a container database. I am interested in the security of this of course and one element that....[Read More]
Posted by Pete On 23/07/13 At 02:52 PM
I just saw a link to a post by Steve Karam on an ISACA list and went for a look. The post is titled " Password Verification Security Loophole ". This is an interesting post discussing the fact that ALTER....[Read More]
Posted by Pete On 22/07/13 At 08:39 PM
I started to ask a question a few blog posts ago about how does the 12cR1 database affect database security audits. I have decided to come back to it now as it is a good chance to do so. I....[Read More]
Posted by Pete On 08/07/13 At 05:31 PM
Everyone was spoiled for the choice of the sessions. I remained focused on the engineered systems and performance tuning. Both conferences soaked me up in plenty of sessions from these tracks and much more. Jonathan Lewis talked about compression, and CBO. Tom Kyte also bestowed with his keen outlook on the various features. Pythian’s folks Luke Davies, Maris, Alex Gorbachev, and Michael Mckee shared their great insight too.
Oracle also hosted ACE dinner one evening, and it was awesome to meet yet again with numerous ACE fellows. Not only was the seven course Chinese dinner was sumptuous, the networking was delicious too. I was also honored to attend the Oaktable network fellows informal dinner at one of the Indian restaurant, and it was awesome too. Pythian and Rittman Mead also hosted drinks and dinner at a Pakistani restaurant and its really something to write home about.
While battling the jet lag the whole week long I was in UK, the proceedings of the week were very engaging and there was so much to learn and share and network. It was refreshingly great to meet yet again with the Christian Antognini, Jonathan Lewis, Heli Helskyaho, Frits Hoogland, Mark Rittman, Gunnar Bjarnason, Kyle Hailey and host of others.
Once it was over, I traveled back from one cold end of the Southern Hemisphere to the other warm end of Northern Hemisphere.
Among the Oracle 12c New Features, there is the addition of an uninstall tool encapsulated in the Oracle database binaries. You do not need to download the deinstallation tool for Oracle products anymore (deinstall utility). This blog posting features an overview of this tool and its functionnalities.
Uninstalling Oracle 12c products
There are two possibilities if you want to uninstall Oracle 12c products (Oracle database, Oracle Grid infrastructure or Oracle client):
- Running the deinstallation tool with the runInstaller command using the parameters –desinstall and –home from ORACLE_HOME/oui/bin/
- Using the deinstallation tool which is also available as a separate command in the ORACLE_HOME deinstall directory after the installation of the Oracle product. It is located in ORACLE_HOME/deinstall. In this posting I am going to speak about the second solution.
This new deinstallation command line tool uses information gathered from the environment and information provided (if necessary) in order to create a parameter file. It is also possible to use the template parameter file located under ORACLE_HOME/deinstall/response. The deinstallation tool automatically stops Oracle software, and removes Oracle software and configuration files on the operating system.
As the Oracle documentation explains in the documentation, it is important to take into consideration the following:
"If the oraInventory contains no other registrered home besides the home that you are deconfiguring and removing, then the deinstall command removes the following files and directory contents in the Oracle base directory of the Oracle Database installation owner
Oracle strongly recommends that you configure your installations using an Optimal Flexible Architecture (OFA) configuration, and that you reserve Oracle base and Oracle home paths for exclusive use of Oracle software. If you have any user data in these locations in the Oracle base that is owned by the user account that owns the Oracle software, then the deinstall command deletes this data."
Using the deinstallation tool is a straightforward process. You simply have to execute the deinstall tool from ORACLE_HOME/deinstall directory a demonstrated below.
Testing the uninstall feature
Let's test this deinstallation tool with the command deinstall from $ORACLE_HOME/deinstall:
-bash-3.2$ cd /u00/app/oracle/product/12.1.0/db_0_1/deinstall/
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /u00/app/oraInventory/logs/
############ ORACLE DEINSTALL & DECONFIG TOOL START ############
######################### CHECK OPERATION START #########################
## [START] Install check configuration ##
Checking for existence of the Oracle home location /u00/app/oracle/product/12.1.0/db_0_1
Oracle Home type selected for deinstall is: Oracle Single Instance Database
Oracle Base selected for deinstall is: /u00/app/oracle
Checking for existence of central inventory location /u00/app/oraInventory
Checking for sufficient temp space availability on node(s) : 'vmtestoel01'
## [END] Install check configuration ##
Network Configuration check config START
Network de-configuration trace file location: /u00/app/oraInventory/logs/netdc_check2012-07-29_03-00-16-PM.log
Network Configuration check config END
Database Check Configuration START
Database de-configuration trace file location: /u00/app/oraInventory/logs/databasedc_check2012-07-29_03-00-17-PM.log
Use comma as separator when specifying list of values as input
Specify the list of database names that are configured in this Oracle home :
Database Check Configuration END
Oracle Configuration Manager check START
OCM check log file location : /u00/app/oraInventory/logs//ocm_check8060.log
Oracle Configuration Manager check END
######################### CHECK OPERATION END #########################
####################### CHECK OPERATION SUMMARY #######################
Oracle Home selected for deinstall is: /u00/app/oracle/product/12.1.0/db_0_1
Inventory Location where the Oracle home registered is: /u00/app/oraInventory
Checking the config status for CCR
Oracle Home exists with CCR directory, but CCR is not configured
CCR check is finished
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/u00/app/oraInventory/logs/deinstall_deconfig2012-07-29_03-00-14-PM.out'
Any error messages from this session will be written to: '/u00/app/oraInventory/logs/deinstall_deconfig2012-07-29_03-00-14-PM.err'
######################## CLEAN OPERATION START ########################
Database de-configuration trace file location: /u00/app/oraInventory/logs/databasedc_clean2012-07-29_03-00-37-PM.log
Network Configuration clean config START
Network de-configuration trace file location: /u00/app/oraInventory/logs/netdc_clean2012-07-29_03-00-37-PM.log
De-configuring backup files...
Backup files de-configured successfully.
The network configuration has been cleaned up successfully.
Network Configuration clean config END
Oracle Configuration Manager clean START
OCM clean log file location : /u00/app/oraInventory/logs//ocm_clean8060.log
Oracle Configuration Manager clean END
Setting the force flag to false
Setting the force flag to cleanup the Oracle Base
Oracle Universal Installer clean START
Detach Oracle home '/u00/app/oracle/product/12.1.0/db_0_1' from the central inventory on the local node :Done
Delete directory '/u00/app/oracle/product/12.1.0/db_0_1' on the local node : Done
The Oracle Base directory '/u00/app/oracle' will not be removed on local node. The directory is not empty.
Oracle Universal Installer cleanup was successful.
Oracle Universal Installer clean END
## [START] Oracle install clean
##Clean install operation removing temporary directory '/tmp/deinstall2012-07-29_03-00-00PM' on node 'vmtestoel01'
## [END] Oracle install clean ##
######################### CLEAN OPERATION END #########################
####################### CLEAN OPERATION SUMMARY #######################
Cleaning the config for CCR
As CCR is not configured, so skipping the cleaning of CCR configuration
CCR clean is finished
Successfully detached Oracle home '/u00/app/oracle/product/12.1.0/db_0_1' from the central inventory on the local node.
Successfully deleted directory '/u00/app/oracle/product/12.1.0/db_0_1' on the local node.
Oracle Universal Installer cleanup was successful.
Oracle deinstall tool successfully cleaned up temporary directories.
############# ORACLE DEINSTALL & DECONFIG TOOL END #############
Do not forget to remove the deinstallation script in /tmp.
In my case, the desinstallation directory in /tmp directory used about 500MB of disk space as presented below:
-bash-3.2$ du -hs /tmp/deinstall2012-07-29_02-58-14PM/
It is possible to specify some options for the desinstallation tool. These options can be listed with the help option such as demonstrated below:
oracle@vmtestoel01:/u00/app/oracle/product/12.1.0/db_0_1/deinstall/  ./deinstall -help
[ -silent ]
[ -checkonly ]
[ -local ]
[ -paramfile ]
[ -params ]
[ -o ]
[ -tmpdir ]
[ -logdir ]
[ -help : Type -help to get more information on each of the above options. ]
Having the deinstallation tool encapsulated into the Oracle Installation Media is definitively an added value compared to the previous version. This tool is easy to use and allows configuring a parameter file in order to use it in a batch process. However, I have encountered strange problems with this tool in some specific context, such as demonstrated below. Unsetting the ORACLE_HOME solved the issue in my case.
Network de-configuration trace file location: /u00/app/oraInventory/logs/netdc_check2012-07-29_02-57-35-PM.log
# A fatal error has been detected by the Java Runtime Environment:
# SIGSEGV (0xb) at pc=0x00007f923401f933, pid=27863, tid=1087699264
# JRE version: 6.0_31-b02
# Java VM: Java HotSpot(TM) 64-Bit Server VM (20.6-b01 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# C [libclntshcore.so.12.1+0x204933] long+0x33
# An error report file with more information is saved as:
# If you would like to submit a bug report, please visit:
# The crash happened outside the Java Virtual Machine in native code.
# See problematic frame for where to report the bug.
An exciting feature of 12c is Automatic Data Optimization (ADO). It can automate Compression Tiering and Storage Tiering. The documentation mentions that Storage Tiering can be based on a customized function, but there is no example for it. Therefore this posting.
[oracle@uhesse ~]$ sqlplus / as sysdba SQL*Plus: Release 126.96.36.199.0 Production on Mon Dec 9 10:33:17 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 188.8.131.52.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> grant dba to adam identified by adam; Grant succeeded. SQL> connect adam/adam Connected. SQL> create tablespace tbs1 datafile '/home/oracle/prima/tbs1.dbf' size 10m; Tablespace created. SQL> create tablespace tbs2 datafile '/home/oracle/prima/tbs2.dbf' size 10m; Tablespace created. SQL> create table t (n number) partition by range (n) (partition p values less than (2) tablespace tbs1); Table created.
Nothing special so far, just two tablespaces and a partitioned table. My customized ADO policy is very simple and uses a working table:
SQL> create table adoflag (n number); Table created.
SQL> insert into t values (1); 1 row created. SQL> commit; Commit complete. SQL> insert into adoflag values (0); 1 row created. SQL> commit; Commit complete. SQL> create or replace function adofunc (object_number in number) return boolean as v_n number; begin select n into v_n from adoflag; if (v_n=1) then return true; else return false; end if; end; / Function created.
Now that function is attached to an ADO policy that is placed on the partition p – notice that Storage Tiering policies can be on the segment level only:
SQL> alter table t modify partition p ilm add policy tier to tbs2 on adofunc; Table altered. SQL> col policy_name for a10 SQL> select policy_name,enabled from dba_ilmpolicies; POLICY_NAM ENABLE ---------- ------ P1 YES
The name of the policy is provided automatically. ADO Tiering requires the following initialization parameter setting, regardless that the customized policy is not based on access respectively modification frequency of the partition p:
SQL> alter system set heat_map=on; System altered.
Attention: Setting this already means you need the Advanced Compression Option! And to me it seems funny that this parameter can be modified on the session level, but that’s how it is. I want to cause now the Storage Tiering, so I update the flag to 1, but that would lead to automatic action only when the next Auto Task maintenance window opens. Therefore I trigger the ADO Tiering manually.
SQL> update adoflag set n=1; 1 row updated. SQL> commit; Commit complete. SQL> select tablespace_name from user_tab_partitions where table_name='T'; TABLESPACE_NAME ------------------------------ TBS1 SQL> declare v_executionid number; begin dbms_ilm.execute_ilm (ilm_scope=>dbms_ilm.scope_schema, execution_mode=>dbms_ilm.ilm_execution_offline, task_id=>v_executionid); end; / PL/SQL procedure successfully completed. SQL> select task_id,policy_name,selected_for_execution from dba_ilmevaluationdetails; TASK_ID POLICY_NAM SELECTED_FOR_EXECUTION ---------- ---------- ------------------------------------------ 7 P1 SELECTED FOR EXECUTION SQL> select tablespace_name from user_tab_partitions where table_name='T'; TABLESPACE_NAME ------------------------------ TBS2
It is possible to add a Storage Tiering policy to a non-partitioned table, but that is probably not a good idea. My observation at least with the current 184.108.40.206 is that open transactions on the non-partitioned table will cause the Storage Tiering to fail silently. Usually, the Storage Tiering will be a unique action on the segment. For testing purposes, though, you may want to trigger it multiple times. But the policy gets silently disabled after the first tiering although dba_ilmpolicies shows it as enabled:
SQL> select policy_name,enabled from dba_ilmpolicies; POLICY_NAM ENABLE ---------- ------ P1 YES SQL> alter table t move partition p tablespace tbs1 online; Table altered. SQL> declare v_executionid number; begin dbms_ilm.execute_ilm (ilm_scope=>dbms_ilm.scope_schema, execution_mode=>dbms_ilm.ilm_execution_offline, task_id=>v_executionid); end; / PL/SQL procedure successfully completed. SQL> select tablespace_name from user_tab_partitions where table_name='T'; TABLESPACE_NAME ------------------------------ TBS1 SQL> select task_id,policy_name,selected_for_execution from dba_ilmevaluationdetails; TASK_ID POLICY_NAM SELECTED_FOR_EXECUTION ---------- ---------- ------------------------------------------ 44 P1 POLICY DISABLED 7 P1 SELECTED FOR EXECUTION SQL> alter table t modify partition p ilm enable_all; Table altered. SQL> declare v_executionid number; begin dbms_ilm.execute_ilm (ilm_scope=>dbms_ilm.scope_schema, execution_mode=>dbms_ilm.ilm_execution_offline, task_id=>v_executionid); end; / PL/SQL procedure successfully completed. SQL> select tablespace_name from user_tab_partitions where table_name='T'; TABLESPACE_NAME ------------------------------ TBS2 SQL> select task_id,policy_name,selected_for_execution from dba_ilmevaluationdetails; TASK_ID POLICY_NAM SELECTED_FOR_EXECUTION ---------- ---------- ------------------------------------------ 45 P1 SELECTED FOR EXECUTION 44 P1 POLICY DISABLED 7 P1 SELECTED FOR EXECUTION
I hope this helps to get an understanding of customized ADO policy functions and to stay clear of some of the pitfalls associated with that topic. As always: Don’t believe it, test it!
Tagged: 12c New Features, ADO, ILM
Alchemists are best known for their (completely fictional and entirely ridiculous, but that’s besides the point) amazing ability to turn lead into gold. Let’s face it, there’s a lot of lead in the Oracle world. Bugs, angry developers, metrics that can seem to elude human understanding…but I digress. The question is, what can an alchemist do when they are already working with gold in the first place?
Refine it, of course. Turn it into something beautiful.
Today I start working with Delphix, a revolutionary Agile Data Platform that is capable of finally providing mobility and ease of use for your huge — and dare I say it, grossly overbloated — data silos. Now, my reading faithful know I’ve talked about Delphix in the past. Heck, I’ve gushed about Delphix in the past. No, it’s more safe to say I’ve gone absolutely crazy over Delphix in the past. Be that as it may, you’ll have to indulge me once more.What is best in life? To crush your processes, see them driven before you, and to hear the lamentation of your project managers. – Conan the CIO
I firmly believe Delphix is gold. Those who have worked personally with me over the last year can attest to that. It’s the first technology I’ve worked with that I can say fulfills the needs of DBAs, the business, project management, developers, and system administrators all in one shot. That alone makes it disruptive. But the way in which it seamlessly integrates into an enterprise ecosystem and delivers immediate results makes it incredible.
Man, that was a good plug. I’m a regular Don Draper, except for the whole boozing and womanizing and smoking and looking great in a fedora thing.
Anyways, I won’t take up more of your precious slacking off time with sales talk. Instead, I’ll tell you that I’m excited at this new opportunity and the company, and I expect that over time I’ll be able to mold, refine, and enrich what I learn with you for your reading pleasure.
Forbes has an interesting, yet scary article, on the cost of data center cloud failure and steps to reduce your risks. Read about it HERE.
Thats a really frequently asked question. Oozie is a workflow manager and scheduler. Most companies already have a workflow schedulers – Activebatch, Autosys, UC4, HP Orchestration. These workflow schedulers run jobs on all their existing databases – Oracle, Netezza, MySQL. Why does Hadoop need its own special workflow scheduler?
As usual, it depends. In general, you can keep using any workflow scheduler that works for you. No need to change, really.
However, Oozie does have some benefits that are worth considering:
- Oozie is designed to scale in a Hadoop cluster. Each job will be launched from a different datanode. This means that the workflow load will be balanced and no single machine will become overburdened by launching workflows. This also means that the capacity to launch workflows will grow as the cluster grows.
- Oozie is well integrated with Hadoop security. This is especially important in a kerberized cluster. Oozie knows which user submitted the job and will launch all actions as that user, with the proper privileges. It will handle all the authentication details for the user as well.
- Oozie is the only workflow manager with built-in Hadoop actions, making workflow development, maintenance and troubleshooting easier.
- Oozie UI makes it easier to drill down to specific errors in the data nodes. Other systems would require significantly more work to correlate jobtracker jobs with the workflow actions.
- Oozie is proven to scale in some of the world’s largest clusters. The white paper discusses a deployment at Yahoo! that can handle 1250 job submissions a minute.
- Oozie gets callbacks from MapReduce jobs so it knows when they finish and whether they hang without expensive polling. No other workflow manager can do this.
- Oozie Coordinator allows triggering actions when files arrive at HDFS. This will be challenging to implement anywhere else.
- Oozie is supported by Hadoop vendors. If there is ever an issue with how the workflow manager integrates with Hadoop – you can turn to the people who wrote the code for answers.
So, should you use Oozie? If you find these benefits compelling, then yes. Step out of your comfort zone and learn another new tool. It will be worth it.
gen-E’s CTO, Dr. Duke Tantiprasut, and RDX’s VP of Sales and Marketing, Rob Brown, will be presenting at Gartner’s Data Center Conference this Wednesday, December 11, at 2:30 PM. During this time, they will explain the benefits of IT process automation through a case study of RDX’s use of gen-E’s Resolve software.
More information can be found in gen-E’s press release here.
If you’re attending, we hope to see you there.
Here’s another of my “draft” notes that needs some exapansion and, most importantly, proof.
I have a fact table with a “status id” column that shows a massive skew. Unfortunately I have a dimension table that holds the “status code”, so (in theory, at least) I have to do a join from the statuses table to the fact table to find rows of a given status. Unfortunately the join hides the skew:
select f.* from facts f, statuses s where s.code = 'C' and f.status_id = s.status_id ;
The optimizer knows that the status_id column on the facts table has a highly skewed distribution and will create a histogram on it, but it can’t know which status code corresponds to which status_id, so the histogram doesn’t help in calculating the join cardinality.
Will a bitmap join index help ? Answer – NO.
It creates a virtual column on the facts table, but you can’t collect stats on the virtual column. The optimizer won’t use the stats even if you create them, all it will use is the number of distinct keys in the index.
For the optimizer to collect the stats the Oracle developers would have to come up with a new way to collect column stats by reading an index on that column – but it would be useful.
Possible workaround – don’t do this join, rewrite the query with an IN subquery with a /*+ precompute_subquery */ hint to generate an IN-list at optimisation time.
Here’s an offering in my “drafts for someone else to finish” suite of postings; it’s one I’ve been meaning to retest and publish for quite some time. It’s a possible answer to a question that comes up occasionally on the OTN database forum: “How do I resynchronize two tables that are supposed to be copies of each other?”
I suppose it’s possible to interpret this question in a couple of ways, but sometimes it means – “anything in table 1 should also be in table 2, anything in table 2 should also be in table 1, and where a row exists it should be exactly the same in both tables”. There are two “philosophical” problems attached to the task, of course – first, how do you decide that two rows that are currently different from each other are supposed to be the same; second how do you decide which bits of which version hold the correct values.
Whatever the detail of your final decision it’s likely to be a messy and, perhaps more importantly, slow job to do. If your requirement is very simple, though, you may not need to write much code to achieve it if you take a look at the package dbms_rectifier_diff (see script dbmsrctf.sql in $ORACLE_HOME/rdbms/admin). The package has been around since at least Oracle 8i though, according to my notes, I didn’t play around with it until March 2002 and 220.127.116.11; it contains procedures to find differences and to rectify differences; here’s a simple demonstration of the use (or possibly abuse) of the package that I haven’t tested in more than 10 years:
rem rem Create the tables to be compared. They MUST have primary keys declared rem drop table t1; drop table t2; create table t1 nologging as select rownum id, to_char(rownum) small_vc, rpad(rownum,100,'x') padding from all_objects where rownum <= 1000 ; alter table t1 add constraint t1_pk primary key (id) nologging; delete from t1 where id in (22, 300, 850); create table t2 nologging as select rownum id, to_char(rownum) small_vc, rpad(rownum,100,'x') padding from all_objects where rownum <= 1000 ; alter table t2 add constraint t2_pk primary key (id) nologging; delete from t2 where id in (55, 475, 760); commit; -- you could gather some stats at this point rem rem Create the report tables rem One table matches the structure of the data being compared rem (which need not be ALL columns in the base tables) rem The other table will be used to list rowids from the first table, rem and tell you which base table it is missing from. rem drop table diff_data; drop table diff_rowids; create table diff_data ( id number, small_vc varchar2(40), padding varchar2(100) ); create table diff_rowids( r_id rowid, present varchar2(4000), absent varchar2(4000) ) ; rem rem First call the DIFFERENCES procedure rem begin dbms_rectifier_diff.differences( sname1 => user, -- reference schema oname1 => 'T1', -- reference table reference_site => null, -- reference site (null => current) db_link sname2 => user, -- target schema oname2 => 'T2', -- target table comparison_site => 'jplcopy', -- target site (null => current) db_link where_clause => 'id <= 1000', -- optional where clause to restrict rows column_list => null, -- e.g. 'id,padding' , null=> all, -- leave no spaces around commas missing_rows_sname => user, -- output schema, missing_rows_oname1 => 'DIFF_DATA', -- table to list mismatching data missing_rows_oname2 => 'DIFF_ROWIDS',-- table to locate mismatching data missing_rows_site => null, -- output site (null => current) db_link max_missing => 10, -- how many differences before stopping commit_rows => 100 -- how many rows to commit in output ); end; / column absent format a30 column present format a30 select * from diff_rowids; column id format 999 column small_vc format a10 column padding format a10 select rowid, id, small_vc, substr(padding,1,10) padding from diff_data ; rem rem Now call the RECTIFY procedure rem This reads the existing DIFF tables, and distributes the data accordingly. rem begin dbms_rectifier_diff.RECTIFY( sname1 => user, -- reference schema oname1 => 'T1', -- reference table reference_site => null, -- reference site (null => current) sname2 => user, -- target schema oname2 => 'T2', -- target table comparison_site => 'jplcopy', -- target site (null => current) column_list => null, -- e.g. 'id,padding' , null=> all, -- leave no spaces around commas missing_rows_sname => user, -- output schema, missing_rows_oname1 => 'DIFF_DATA', -- table to list mismatching data missing_rows_oname2 => 'DIFF_ROWIDS',-- table to locate mismatching data missing_rows_site => null, -- output site (null => current) commit_rows => 100 -- how many rows to commit in output ); end; /
According to the header notes in the dbmstrctf.sql script, nothing changed between 1991 and 2010 (11.1), and then a couple more comments appear in the 12c timeline. I won’t make any guarantees about how the package behaves (the code, when traced in 9.0, was dire) but perhaps this script will be a pointer to a good enough solution for people facing the need to resynchronise data sets occasionally.Footnote:
It was a discussion of this type of problem at OpenWorld 2013 – and a comment about a licensed option using the package dbms_comparison – that prompted me to resurrect this very old script.