Skip navigation.

Feed aggregator

Oracle Security And Delphix Paper and Video Available

Pete Finnigan - Wed, 2016-05-04 15:05

I did a webinar with Delphix on 30th March 2016 on USA time. This was a very good session with some great questions at the end from the attendees. I did a talk on Oracle Security in general, securing non-production....[Read More]

Posted by Pete On 01/04/16 At 03:43 PM

Categories: Security Blogs

3 Days of Oracle Security Training In York, UK

Pete Finnigan - Wed, 2016-05-04 15:05

I have just updated the public Oracle Security training dates on our Oracle Security training page to remove the public trainings that have already taken place this year and to add a new training in York for 2016. After the....[Read More]

Posted by Pete On 31/03/16 At 01:53 PM

Categories: Security Blogs

Oracle Data Masking and Secure Test Databases

Pete Finnigan - Wed, 2016-05-04 15:05

My daily work is helping my customers secure their Oracle databases. I do this in many ways from performing detailed security audits of key databases to helping in design of secure lock down policies to creating audit trails to teaching....[Read More]

Posted by Pete On 14/03/16 At 08:45 AM

Categories: Security Blogs

BOF: A Sample Application For Testing Oracle Security

Pete Finnigan - Wed, 2016-05-04 15:05

In my Oracle security training classes I use a couple of sample applications for various demonstrations. I teach people how to perform security audits of Oracle databases, secure coding in PL/SQL, designing audit trail solutions and locking down Oracle. We....[Read More]

Posted by Pete On 10/03/16 At 11:07 AM

Categories: Security Blogs

Two New Oracle Security Presentations Available

Pete Finnigan - Wed, 2016-05-04 15:05

I attended the UKOUG conference last week Monday to Wednesday in Birmingham. This is the first year for three years that it has been back at the ICC in the center of Birmingham. The last two years have seen the....[Read More]

Posted by Pete On 14/12/15 At 08:54 PM

Categories: Security Blogs

Oracle Security Training In York

Pete Finnigan - Wed, 2016-05-04 15:05

We ran a five day Oracle Security training event in York, England from September 21st to September 25th at the Holiday Inn hotel. This proved to be very successful and good fun. The event included back to back teaching by....[Read More]

Posted by Pete On 22/10/15 At 08:49 PM

Categories: Security Blogs

New Presentation - Building Practical Oracle Audit Trails

Pete Finnigan - Wed, 2016-05-04 15:05

I wrote a presentation on designing and building practical audit trails back in 2012 and presented it once and then never again. By chance I did not post the pdf's of these slides at that time. I did though some....[Read More]

Posted by Pete On 01/10/15 At 05:16 PM

Categories: Security Blogs

Protect Your APEX Application PL/SQL Source Code

Pete Finnigan - Wed, 2016-05-04 15:05

Oracle Application Express is a great rapid application development tool where you can write your applications functionality in PL/SQL and create the interface easily in the APEX UI using all of the tools available to create forms and reports and....[Read More]

Posted by Pete On 21/07/15 At 04:27 PM

Categories: Security Blogs

Enjoy these Snappy Video Clips from Oracle's Modern Customer Experience

Linda Fishman Hoyle - Wed, 2016-05-04 11:22
Oracle's Modern Customer Experience held in Las Vegas, NV, April 26-28, 2016, proved to be an amazing gathering of more than 4,200 CX professionals, including marketers, sales people, customer service executives, and e-commerce professionals. It was a high-energy conference powered by masterful presentations from Troy Carter, founder/CEO of Atom Factory, entrepreneur, investor, and manager, and Mark Hurd, Oracle co-CEO.

The vibe was felt all over the MGM compound, but even more so in the demo and exhibit area. This is where video mavens Ruth Kale-Fok, Maureen Boctor, Brian Mock, David Hope-Ross, Martin Taylor, and Kathryn Perry were working their camera and interview magic.

You know, some people freeze when they are asked to speak on camera.

Generally, analysts and influencers don't fall into that camp.

True to form, the analysts and influencers at the conference were more than happy to share their viewpoints on the importance of customer experience and the role Oracle is playing with its end-to-end solution.

Watch this short video starring Dr. Natalie Petouhoff from Constellation Research, Keith Dawson and Aphrodite Brinsmead from Ovum, Rebecca Wetterman from Nucleus Research, and Mark Smith from Ventana Research.

They also captured some great quotes from conference attendees in these video clips:

Want to grow your revenue? Ask our CEO how …

Linda Fishman Hoyle - Wed, 2016-05-04 11:02
At the Modern CX Experience last week in Las Vegas, Oracle's co-CEO Mark Hurd told the audience that CEOs have about 18 quarters (4.5 years) to prove themselves before activist shareholders get involved. In an environment where global economic growth is anemic at best and rapidly declining at worst (consider Russia, Brazil, etc.), most CEOs look for quick ways to gain upside on earnings vs. revenue.
Many turn to slashing expenses as a sure way to deliver desired earnings results. Others try to build new products or lower prices to lure customers from the competition. But what they miss is that their success, and that of their company, lies with their existing customers. The American Customer Satisfaction Index says:
Companies with happier customers give five times the return than companies with unhappy customers.
A 5x return would validate any CEO―and thrill his or her board members and shareholders. But pushing good customer service to get happier customers isn’t exactly jazzy or unique for a nervous CEO who needs to show results quickly.
However, the research doesn’t lie; nor do our personal experiences. In his keynote, Mark shared his frustration about his bank and his US-based cell phone service provider. Both companies have siloed processes which translate into unnecessary and irritating interactions―and plenty of lost opportunities to engage and delight customers like him.
So Mark’s advice to the CEOs and professionals in the audience was to step up their customer experience games with a suite of integrated applications. “There are lots of CX providers so it’s easy to go down the path of more silos; you’ll get some benefits, innovation, but the suite is the real benefit,” said Mark.  The recipe for success is really pretty simple―get to know your customers’ wants and needs and serve them well.
Takeaways:
Oracle is the only vendor with an integrated suite.
Oracle is the leader.
Oracle is committed to providing best-of-breed integrated solutions in each area.
Oracle can help every CEO move closer to that 5x return.

Go Mobile and Upgrade your Oracle Forms with Auraplayer

WebCenter Team - Wed, 2016-05-04 09:38

Author: Mitchell Palski – Oracle Fusion Middleware Specialist

Let’s start off with this? Why would you upgrade your Oracle Forms applications? Oracle Forms Builder and Oracle Forms Services have been upgraded to simplify the development and deployment of Forms applications on the Web. In a world where very few applications stand-alone and need to integrate into their Enterprise, Oracle recognizes the importance of being able to persist your legacy Forms applications into the future while enabling your Service-Oriented Architecture to leverage their capabilities. The future of Forms includes improving the Java-based Web user interface and extending product "openness" by allowing Java integration on all three tiers

Aside from improving your traditional Oracle Forms offering, there is a new consideration that many of us have never had to deal with in the past – mobile access. Oracle Forms and Reports are such convenient tools for developing applications for our mobile workforce, but yet they’ve been restricted to the use of laptops and desktop machines. Here’s the good news – Oracle partner Auraplayer has the skillsets and the software to help your organization quickly and efficiently overcome that obstacle.

Why did I say “skillset”? Well, Auraplayer has some really talented people in their management ranks that have been working with Oracle’s Fusion Middleware technology for a long time. One of the challenges that I had when I first heard about Auraplayer was understanding their value for my customers, so I talked to CEO Mia Urman and she helped explain to me that Auraplayer isn’t just selling a product (you’ll hear about that in a minute), they’re also helping customers upgrade. Oracle Forms has been around for so long that there aren’t a ton of projects out there that have the same personnel on staff as when they first started. Auraplayer brings the industry expertise to mitigate your risks and deliver a successful Forms upgrade so your team can start taking advantage of all the new features 12c has to offer.

Upgrades aren’t the most exciting projects in the world, so woopty-doo right? Well, the reason that I’m excited is because if you’re working with Auraplayer to upgrade your Forms application why not let them tell you about their Mobile offering while they’re their? I won’t try to go through all of their features in this post today, but let me give you the highlights (from my perspective):
  1. You don’t have to change your Forms applications. Re-writing code to enable a mobile UI can be costly, it can take a long time to complete, and it requires support from your development team after deployment.
  2. Auraplayer will REST-enable your Forms application so you can consume services across the Enterprise, making integration easy and mobile development fast and efficient.
  3. Couple Auraplayer with Oracle Mobile Cloud Service and you have a complete project plan from start to finish.
    • Define and enable processes
    • Automatically generate services
    • Develop UI and consume services – works on any mobile device

So, do your end users have smart phones and tablets? Yes they do. Do you need up upgrade your legacy Forms apps and get those users actively using them in the field? You tell me!

New York City Metropolitan Transportation Authority (NYC MTA) figured out a way to use Auraplayer to “work smarter, not harder” by leveraging Auraplayer. Read about their success here: https://blogs.oracle.com/mobile/entry/new_york_mta_mobile_cloud 

If you want to learn more, check out these videos by Grant Ronald:

What is Oracle Cloud Management Pack in Enterprise Manager Cloud Control?

Arun Bavera - Wed, 2016-05-04 09:28
Cloud Management pack basically consists of these features:
These packs are basically used to setup your private in-house Cloud Services ( MWaaS, DBaaS, IaaS, PaaS, SCHaaS, SnapClone etc…)
Without these packs we cannot use any self-service provisioning features.
With DBLM you can use Deployment Procedure to create/clone new DB’s but you cannot use self-service portal.
The traditional DB LifeCycle Management provides all the management of DB but there are some exception of features like below:
Note: The ability to create 'new' full clones is licensed under Database Lifecycle Management Pack for Oracle Database, but the additional 'refresh' function requires licensing the Cloud Management Pack for Oracle Database.
clip_image001
clip_image002

Let us discuss a Scenario where Customer may require Hybrid Cloud Management capabilities:
Customer OMS is running on-premise and they have some cloud services subscriptions also.
If they just want to manage their cloud service targets they can use Hybrid Agent to manage making sure they have the traditional license pack for those targets to have single pane of view of all their on-premise traditional targets and Cloud targets.
They can also use the REST API to manage and view their cloud services which comes with cloud service offerings.
If the customer is not going to build any in-house private cloud service ( MWaaS, DBaaS, IaaS, PaaS, SCHaaS, SnapClone etc…) using OEM, they may not require Cloud Management packs.
Cloud Management pack provides top up features to their traditional packs.
Oracle Cloud Management Pack for Oracle DatabasePrerequisitesThe Oracle Cloud Management Pack for Oracle Database requires the Database Lifecycle Management Pack for Oracle Database.
 
As of now to to raise a request to create an instance of DBCS or JCS  on Oracle Cloud  you have to use REST API or  Cloud UI.
Hope we will soon have integration in OEM cloud management pack capability to raise service request directly against Oracle Cloud Service ( Cloud service hosted at  Oracle Data center or at customer as Private Cloud Service)
which will create an instance in cloud and will discover the assets and manage in OEM .






Categories: Development

Transforming Analytics and Reporting

PeopleSoft Technology Blog - Wed, 2016-05-04 09:12

Content contributed by Balaji Pattabhiraman

A few months ago, I wrote in this blog about a feature in PeopleTools called Simplified Analytics.  This is really transformative technology that puts tremendous power in the hands of end users, enabling them to perform ad-hoc analytics right in context of their transactions and business processes.  It makes reporting timely, secure, and relevant.

Let's expand on that concept and take advantage of some other PeopleTools featues.  In this case, imagine I am a manager or subject area expert.  I've created several simplified analytics that I want to make available to my team for use in their daily work.  Simplified analytics enables me to create a tile from any analytic I create.  Those tiles can then be placed on any Fluid home page or Dashboard, which is another new PeopleTools feature.  Lets see an example of this.

As a business administrator I've created several simplifed analytics for the My Team page.  These are available in the My Analytics tab in Related Information.

Note that each tab on this page--Summary, Performance, Compensation, and Leave Balances--contains different analytics relevant to each topic.  I've created these analytics  to provide better decision support for these processes.

This calls for a quick review of an important part of the Simplified Analytics process.  When creating or editing the analytic, I have the option of publishing the analytic so it can be used by others.  (See this post for more info on creating a Simplified Analytic.)



When I choose to publish, I have the option of publishing to the My Analytics section of the Related Actions frame or publishing to the Tile Repository. Since I'll be creating an analytic dashboard, I'll save to the repository.



Now I can go to the Personalize option from the menu in any home page.  From here I can create my new analytic home page.  (This could be created as a Dashboard as well.)



In this case, I'll create a new home page called Team Analytics.  This will provide a single access point for our group of analytics, making them available in a convenient form to all team members.



Now that I've created the new home page, I can add tiles representing the analytics that were created previously.



I'll add several analytic tiles and make this home page a robust and valuable analytic tool.



Once I save the page it is displayed.  Here you see the home page with all the tiles I've added.



Now let's publish the home page and make it available to the team so all can benefit from it.  (Note: If I don't publish the home page to other users I can still use it as a personal home page.  One might do this for analytics that are used exclusively by me.) 



Enter a name and label and any other field data necessary. In this case I'm making it public, but in many cases you will want to make a page like this available only to a particular role or permission list.

Now when someone on our team logs in, (in this case Rosanna) she will have access to the new Team Analytics page.

Note that the data from the analytics correspond to Rosanna's team.  If the same analytics were published to a different group, the data would be relevant to that group.  The data, context, and security are determined by the PS Query that forms the basis of the analytic.

This illustrates how you can extend the power of Simplified Analytics to make valuable decision support available to teams, not just individuals.

About Oracle: DBA_DEPENDENCY_COLUMNS

Jeff Moss - Wed, 2016-05-04 07:17

A colleague asked if there was a way to do column level dependency tracking recently. He wanted to know for a given view, which tables and the columns on those tables, it was dependent upon, without, of course, reading through the code.

I was vaguely aware that since 11gR1 Oracle has been tracking fine grained (column) dependencies, but couldn’t find a way of seeing the details stored, until I found this interesting article from Rob Van Wijk:

About Oracle: DBA_DEPENDENCY_COLUMNS

I passed the details on to our DBA who implemented it and it seemed to work, for us. Your mileage may vary, of course.

Enable Copy n Paste in SQL*Plus command line on Windows

Jeff Moss - Wed, 2016-05-04 00:04

My colleague asked me yesterday how to enable copy and paste in the command line SQL*Plus window on Windows 7 – a simple enough task…

On the shortcut that starts the command line version of SQL*Plus, right click and bring up the Properties dialog. Nagivate to the Options tab and make sure the QuickEdit mode is checked on, as below:

image

Now start SQL*Plus and you’ll find that you can hold the left mouse button down whilst dragging a selection area and then pressing return copies the selected text, whilst pressing the right mouse button pastes the copied text.

If you’d prefer to read this from a Microsoft source, try here, where other methods of setting this up are detailed as well as enabling the Autocomplete facility.

Samsung Developers Conference 2016: A More Connected Future

Oracle AppsLab - Tue, 2016-05-03 19:31

After a whirlwind day at Modern CX, I hurried my way back up to San Francisco for the last day of the Samsung Developers Conference 2016. The morning started out exciting with a giveaway gift of the Samsung Gear 360 Camera.

Oh i have plans for you

Intro to PaaS

Pat Shuff - Tue, 2016-05-03 15:44
Today we are going to move up the stack. We will first focus on the Oracle solutions talking about the different platform as a service offerings. It is important to spend a little time reviewing this layer because what one company calls PaaS, another calls SaaS. The best way to get started is to go to cloud.oracle.com and look at the pull downs at the top of the screen. We see Infrastructure, Platform, and Applications.

When we pull down the Platform menu we see that there are different areas that we can dive into.

Data management is the first area that we will review. This is basically a way to aggregate and look at data. We can store data in a database, store on-premise databases into the cloud, store data in NoSQL repositories, and do analytics on a variety of data with Big Data Preparation and Big Data services. All of these involve pulling data into a repository of some type and performing queries against the repository. The key difference is the way that the data is stored, how we can ask questions, and the results that we get back. At this point we will not dive into any of these deeply but at a later point dive deep into the database and database backup.

The Application Development is moving farther away from the technology of storing data and moving closer to how we present data to users. The Java platform, for example, allows us to do things like create a shopping cart or hosting more complex applications in a Java repository or container. The Mobile Cloud Service allows us to dive into existing applications and present a user interface to iPhones, Android Phones, and tablets. The idea is to customize existing web and fat clients into a mobile format that can be consumed on mobile devices. The Messaging Cloud Service is a messaging protocol that allows for transactions in the cloud. If you are looking at connecting different cloud services together it allows you to serialize the communication between vendors for a true transactional experience. The Application Container Cloud is a lightweight Java container allowing you to upload and run java applications but without access to the operating system. This is a shared multi-tenant version of a WebLogic server. The Developer Cloud Service is a DevOps integration for the Java and Database services. This service is an aggregation of public domain components used to develop microservices at the database or java layer. The Application Builder Cloud Service is a cloud based REST api development interface allowing you to integrate with Application software in the Oracle Cloud as well as other Clouds. The API Catalog is a way of publishing the REST apis that you have and expose them to your customers.

The Content and Process Cloud Services are an aggregation of services that address group communications as well as business process flow. The Documents Cloud Service is a way of file sharing on the web. The Process Cloud Service is an extension that allows you to launch business processes (think Business Process Manager or BPM) in the cloud. The Sites Cloud Service is a web portal interface that takes documents and processes and aggregates them into a single cloud site allowing you to take a wiki like presentation but put business processes into the presentation. The Social Network Cloud Service allows you to integrate social network services like Facebook and Twitter into your web presence. It allows you to integrate these services as well as search these repositories for information relating to your company.

The Business Analytics part of Platform services provides data visualization and analytic tools as well as data aggregation utilities. The Business Intelligence component is the traditional BI package that allows users to create custom queries into your database. The Big Data Preparation allows you to aggregate data from a variety of sources into a Big Data repository. The Big Data Discovery allows you to look at your data in a variety of ways and generate reports based on your data and views of data. The Data Visualization Cloud Service allows you to view and analyze your data from different perspectives. This is similar to the BI and Big Data but looks at data slightly differently. The Internet of Things Cloud Service allows you to aggregate monitoring and measuring devices into a repository.

The Cloud Integration part of Platform services is the traditional data aggregation tools from other repositories. The Integration Cloud Service allows you to aggregate traditional SaaS vendors to unify fields like how a customer is defined or what data elements are incorporated into a purchase order. The SOA Cloud Service is implementation of the Oracle SOA Suite in the cloud. The GoldenGate Cloud Service is an implementation of the Oracle Golden Gate software that allows you to take data from different databases and synchronize the different repositories independent of the database vendor. The Internet of Things Cloud Service is the same listed in the Business Analytics section mentioned before.

The Cloud Management part of Platform services allows you to take the log files that you have inside your data center and analyze them for a variety of things. You can aggregate your log files into the Log Analytics Cloud Services to look for patterns, intrusion attempts, and problems or issues with services. The IT Analytics Cloud Service looks at log files and looks for trends like disks filling up, processors being used or not used appropriately. The Application Performance Cloud Service looks at log files to look at how systems and applications are operating rather than how systems are working rather than how components are working.

In Summary, we looked at an overview of the Platform as a Services offered by Oracle. Unfortunately, the variety of topics are too great for one blog. We did a high level overview of these services. In upcoming blogs we will dive deeper into each of these services and look at not only what they are but how they work and how to provision these services. We will also compare and contrast how these services compare to services offered by Amazon and Azure as we dive into each service.

Red Samurai ADF Performance Audit Tool Major Update v 5.0

Andrejus Baranovski - Tue, 2016-05-03 13:42
Our ADF performance audit tool is growing and getting more advanced with each release. Current major update v 5.0 brings ADF Click History integration and allows to track ADF UI client request time. This allows to understand, how long it takes to execute action from user perspective. Combined with ADF BC performance monitoring, we could give precise answer about performance bottlenecks from top to bottom.

New features in v 5.0:

1. Redesigned UI with ADF 12.2.1 Responsive template. UI is aligned with Alta UI best practices

2. Group by ECID functionality. We can track request action from top to bottom. This includes client request time (time needed to complete action on UI, including network traffic time). Executed SQL queries and any issues in ADF BC performance for the given request

3. ADF Click history logging. Click history data about UI performance is being intercepted and logged into Red Samurai DB, for analysis. This gives a database of all user requests and time for each request

4. User request statistics visualization dashboard

Here you can see sample data from ADF demo application. User request statistics are presented to help in understanding system performance. Time for each user request is visualized, along with detail information about request (component ID, name, type, etc.). Average times are presented, along with user statistics and top actions. We display total requests count vs. recent requests, to visualize the load on the system:


Each of the logged requests can be tracked down by ECID. Here we can see a list of VO's invoked during UI request and any slow performance behavior happening in ADF BC:


In the next updated v 6.0, we are going to implement UI behavior analysis, to extract most common UI usage patterns in ADF application.

Transparent Data Encryption for SQL Server in an Availability Group

Pythian Group - Tue, 2016-05-03 12:24

With the all new features in SQL Server 2016 always on, which you can read up on here, it’s easy to forget about Transparent Data Encryption (TDE). This blog post will focus on TDE.

TDE encrypts database files at rest. What this means is your .MDF and .NDF Files, and consequently your backups, will be encrypted, meaning you will not be able to detach the database files and restore them on another server unless that server has the same certificate that was used to encrypt the database.

In this blog post I am using SQL Server 2014 and will explain how to enable TDE on an existing AG Group database

  1. The first thing we need to check is if the server has a master encryption key on all replica in the AG Group
USE MASTER
GO
SELECT * FROM sys.symmetric_keys
WHERE name = '##MS_DatabaseMasterKey##'

The Screenshot below shows I don’t have a key so I need to create one

No Master Encryption Key

  1. Create a Database Master Encryption Key on each of the replicas in the AG Group. It is important to use a complex password

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'C&mpl£xP@$$Wrd'
GO

  1. Run the code in step 1 and this time you should see the below

Master Encryption Key

  1. Now we need to create a certificate to use for the encryption of the database on the primary replica. This can be accomplished by using the below

CREATE CERTIFICATE BackupEncryptionCert
WITH SUBJECT = 'SQL Server 2014 AdventureWorks2012 Encryption Certificate';
GO

  1. Validate the Certificate

SELECT name, pvt_key_encryption_type_desc, thumbprint FROM sys.certificates

Validate Encryption Key

The thumbprint will be useful because when a database is encrypted, it will indicate the thumbprint of the certificate used to encrypt the Database Encryption Key.  A single certificate can be used to encrypt more than one Database Encryption Key, but there can also be many certificates on a server, so the thumbprint will identify which server certificate is needed

  1. Next We need to backup the certificate on the Primary Replica

BACKUP CERTIFICATE BackupEncryptionCert
TO FILE = ‘C:\BackupCertificates\BackupEncryptionCert.bak’
WITH PRIVATE KEY ( FILE = ‘C:\BackupCertificates\BackupEncryptionCertKey.bak’ ,
ENCRYPTION BY PASSWORD = ‘Certi%yC&mpl£xP@$$Wrd’)

Encryption Files

The BACKUP CERTIFICATE command will create two files.  The first file is the server certificate itself.  The second file is a “private key” file, protected by a password. Both files and the password will be used to restore the certificate onto other instances.

  1. The Files created in step 6 needs to be copied to each of the other replicas and created in SQL Server. After the files are copied the below command can be used to create the certificates

CREATE CERTIFICATE BackupEncryptionCert
FROM FILE = ‘C:\BackupCertificates\BackupEncryptionCert.bak’
WITH PRIVATE KEY (FILE = ‘C:\BackupCertificates\BackupEncryptionCertKey.bak’,
DECRYPTION BY PASSWORD = ‘Certi%yC&mpl£xP@$$Wrd’);

  1. That’s all the configuration needed for each instance now we are ready to start encrypting the database. We now need to tell SQL Server which Encryption Type we want to use and which certificate to use. This can be done using the following code on the Primary Replica

Use Adventureworks2012
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE BackupEncryptionCert

  1. Finally, the last step is to enable TDE by executing the below command on the Primary Replica

ALTER DATABASE AdventureWorks2012 SET ENCRYPTION ON

 

And that’s it, I hope you enjoyed this tutorial and found it informative. If you have any questions, please comment below.

Categories: DBA Blogs

Compression -- 6 : Advanced Index Compression

Hemant K Chitale - Tue, 2016-05-03 08:23
Earlier, I had covered Index (Key) Compression which is included in the Enterprise Edition.

In Key Compression, the DBA must specify the Prefix (i.e. number of leading columns in a composite index) that must be used as the compression key.

12.1.0.2 Advanced Index Compression does not require the DBA to manually identify the prefix key length.  Advanced Index Compression auto(magically) identifies the optimal prefix columns.
(Note : Advanced Index Compression requires the Advanced Compression licence Option and is available only in 12.1.0.2 and higher)

SQL> create table target_data as select * from source_data where 1=2;

Table created.

SQL> insert /*+ APPEND */ into target_data select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into target_data select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL> insert /*+ APPEND */ into target_data select * from source_data;

367156 rows created.

SQL> commit;

Commit complete.

SQL>


Creating a Key Compression Index by specifying the Prefix size (the leading 2 columns) :

SQL> create index target_data_ndx_1_comp on
2 target_data (owner, object_type, object_name) compress 2;

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_1_COMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_1_COMP'
4 /

LEAF_BLOCKS
-----------
5508

SQL>


Note how I specified "2" as the Prefix size as I want to compress on repeated values of (OWNER, OBJECT_NAME).

Using Advanced Index Compression by specifying "COMPRESS ADVANCED LOW" and letting Oracle decide on the compression strategy in each leaf block :

SQL> drop index target_data_ndx_1_comp;

Index dropped.

SQL> create index target_data_ndx_2_advcomp on
2 target_data (owner, object_type, object_name)
3 compress advanced low;

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_2_ADVCOMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_2_ADVCOMP'
4 /

LEAF_BLOCKS
-----------
2277

SQL>


Wow, that's significantly smaller.  What's more, I did not have to spend time analyzing the data and the index definition to identify the "correct" Prefix size.

However, it is now possible to specify the entire composite key as the Prefix, although that is not what I would have done in earlier versions.  Identifying the Prefix size requires analyzing the data.

SQL> create index target_data_ndx_1_comp on
2 target_data (owner, object_type, object_name) compress 3
3 /

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_1_COMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_1_COMP'
4 /

LEAF_BLOCKS
-----------
2277

SQL>


So, the Advanced option allows me to let Oracle automatically decide the appropriate mechanism to compress the Index keys.

Just for comparison, here is a regular index :

SQL> drop index target_data_ndx_2_advcomp;

Index dropped.

SQL> create index target_data_ndx_3_nocomp on
2 target_data (owner, object_type, object_name)
3 /

Index created.

SQL> exec dbms_stats.gather_index_stats('','TARGET_DATA_NDX_3_NOCOMP');

PL/SQL procedure successfully completed.

SQL> select leaf_blocks
2 from user_indexes
3 where index_name = 'TARGET_DATA_NDX_3_NOCOMP'
4 /

LEAF_BLOCKS
-----------
7289

SQL>


That is a much larger regular index !
.
.
.

Categories: DBA Blogs