Feed aggregator

Sorting concatenated number list inside a string column

Tom Kyte - Wed, 2017-08-02 22:46
Hello, I have a column in the table that has a current value for example '29|1|3004|3|2|24' I want to rearrange the value in the order to '|1|2|3|24|29|3004' I'm trying to do that without creating temp table or any holding place is there way to do ...
Categories: DBA Blogs

Performance tuning a SQL query

Tom Kyte - Wed, 2017-08-02 22:46
Hello, I need to increase the performance of the following query,Please suggest alternative,this is taking longer time in production. SELECT * FROM ( SELECT MAX (IDN_HEADER_DATA_EXCH) AS IDN_HEADER_DATA_EXCH_MAX, NBR_...
Categories: DBA Blogs

SQL Execution Speed depending on Line Breaks in Query

Tom Kyte - Wed, 2017-08-02 22:46
Dear Tom, if I execute in Toad a query like: select * from SOMESCHEMA.TABLE_A A join SOMESCHEMA.TABLE_B B on B.A_ID = A.A_ID join SOMESCHEMA2.TABLE_C C on A.C_ID = C.C_ID join SOMESCHEMA.TABLE_D D on B.D_ID = D.D_ID where C.C_ID = 1234; ...
Categories: DBA Blogs

By DBA user through After Logon Triggers

Tom Kyte - Wed, 2017-08-02 22:46
Hi, we have schema owner user named ABC, that execute our dayend jobs and as per application requirement it required a DBA role as the application is in development phase, what i needed to do is to fix that only a machine with specific IP address...
Categories: DBA Blogs

Check BI Publisher's Health with BIP Analyzer for E-Business Suite

Steven Chan - Wed, 2017-08-02 18:15

In addition to helping customers resolve issues via Service Requests, Oracle Support also builds over 60 free diagnostic tools for Oracle E-Business Suite 12.2, 12.0, 12.1, and 11i. These Support Analyzers are non-invasive scripts that run health-checks on your EBS environments. They look for common issues and generate standardized reports summarizing that provide solutions for known issues and recommendations on best practices.

Here's an index to these tools:

Spotlight on BIP Analyzer

BI Publisher for EBS (BIP, previously called XML Publisher) is integrated into the E-Business Suite technology stack.  The BIP Analyzer is available here:

The BI Publisher Analyzer reviews BIP configurations and compares them against Oracle's best practices. It provides troubleshooting advice for common issues, such as:

  • Java Heap sizing
  • OPP memory optimization
  • Issues with threads and processes
  • Out of Memory issues

This tool can be run manually or configured to run as a concurrent request, so it can be scheduled to be run periodically and included in regular Workflow Maintenance cycles.

Can this script be run against Production?

Yes. There is no DML in the Analyzer Script, so it is safe to run against Production instances to get an analysis of the environment for a specific instance. As always it is recommended to test all suggestions against a TEST instance before applying to Production.

Related Articles


Categories: APPS Blogs

Seaman Paper Asia Streamlines Business Operations with NetSuite OneWorld

Oracle Press Releases - Wed, 2017-08-02 13:00
Press Release
Seaman Paper Asia Streamlines Business Operations with NetSuite OneWorld Wrapping Paper Supplier Speeds Processes and Boosts Revenue 25 Percent Since Upgrading from MYOB

SAN MATEO, Calif. and HONG KONG—Aug 2, 2017

Oracle NetSuite Global Business Unit, one of the world’s leading providers of cloud-based financials / ERP, HR, Professional Services Automation (PSA) and omnichannel commerce software suites, today announced that Seaman Paper Asia (SPA), a leading B2B supplier of lightweight decorative tissue papers for retail packaging and consumer products, has realised significant business benefits since replacing MYOB accounting software with NetSuite OneWorld in 2013. Based in Hong Kong with two locations in China, and one in Vietnam, SPA relies on NetSuite OneWorld to manage its mission-critical financial processes, distribution, inventory and order management, and customer relationship management (CRM) as well as multi-currency transactions in U.S., Hong Kong, and Singapore dollars and the Euro. Since deploying NetSuite OneWorld, the company has reduced the time needed to manage key business processes by half, and has seen a double-digit increase in top-line revenue.

With its previous MYOB system, SPA struggled with limited reporting capabilities and inefficient double-entry of data. Additionally, the company could not easily scale and lacked the CRM and international capabilities it needed for growth. SPA selected NetSuite OneWorld to gain the scalability and flexibility it.

“Our goal is to be a truly global supplier, but we wouldn’t have been able to sustain our business on MYOB,” said Sam Jones, office manager at SPA. “NetSuite’s powerful customisations and access to information anywhere streamlines our processes and helps us grow.”

Since going live on NetSuite OneWorld, top-line revenue has increased 25 percent at SPA, which produces gift wrapping paper for consumers, as well as branded wrapping paper for footwear and apparel brands such as Prada, Victoria’s Secret and Louis Vuitton. SPA has eliminated the manual process of creating separate sales and purchase orders in MYOB, as NetSuite automatically generates a PO from a sales order, reducing time required by more than one-half. “All our processes have been streamlined, from sales orders to invoicing and fulfillment,” Jones said.

In addition, SPA has greatly improved visibility into key business metrics with customisable NetSuite dashboards and robust reporting and analytics that allow users to drill down from transactions into details. A single source of unified data makes it easy to track trends, identify challenges and opportunities, and continuously optimise the business.

“NetSuite gives us a very powerful reporting and analysis environment,” Jones said. “I’ve set up my dashboard in a way that I can see a cross section of sales performance data — it’s all very intuitive, easy and at my fingertips.”

“SPA is a great example of how organisations in Hong Kong and Southeast Asia can improve business performance with NetSuite”, said Herman Yong, Chief Solution Architect at One Pacific Services, a NetSuite Solution Provider in Hong Kong that implemented OneWorld at SPA.

“We’ve seen soaring demand in Hong Kong and Southeast Asia at large for cloud-based business management software that offers scale and agility not possible with outdated systems,” Herman said. “The results that SPA has seen reflect just how transformational moving to NetSuite is for companies in a range of industries.”

NetSuite OneWorld supports 190 currencies, 20 languages, automated tax calculation and reporting in more than 100 countries, and transactions in more than 200 countries.

Additional benefits that SPA has realised with NetSuite OneWorld include:

  • Multi-currency transactions. NetSuite OneWorld enables SPA to streamline transactions in U.S., Hong Kong and Singapore dollars and the Euro, while Seaman Paper Europe transacts in the Euro and U.S. dollar.
  • International tax compliance. Working with NetSuite Solution Provider Alta Via, the German subsidiary uses NetSuite OneWorld to improve its European Union and Germany tax compliance.
  • Anytime, anywhere cloud access. Ability to access business-critical information in NetSuite OneWorld from anywhere in the world has proven vital to help SPA staff keep their fingers on the pulse of the business while traveling or working remotely.
  • Inventory visibility. SPA has real-time insights into inventory availability at warehouses in Hong Kong, Germany and the U.K., helping it better fulfill orders and manage production volume.
  • CRM capabilities. NetSuite CRM gives SPA sales staff a unified record of all customer account, interaction and order history information to help drive sales and provide a superior customer experience.
About Oracle NetSuite Global Business Unit

Oracle NetSuite Global Business Unit pioneered the Cloud Computing revolution in 1998, establishing the world’s first company dedicated to delivering business applications over the internet. Today, Oracle NetSuite Global Business Unit provides a suite of cloud-based financials / Enterprise Resource Planning (ERP), HR and omnichannel commerce software that runs the business of companies in more than 100 countries. For more information, please visit http://www.netsuite.com.

Follow Oracle NetSuite Global Business Unit’s Cloud blog, Facebook page and @NetSuite Twitter handle for real-time updates.

About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at oracle.com.


Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Industry Customers Modernize Business with Oracle Cloud

Oracle Press Releases - Wed, 2017-08-02 10:46
Press Release
Industry Customers Modernize Business with Oracle Cloud Oracle Global Business Unit cloud solutions drive innovation and accelerate success

Redwood Shores, Calif.—Aug 2, 2017

Companies in growing numbers across industries are gaining new efficiencies while modernizing their businesses with Oracle Cloud. Retail, Hospitality, Financial Services, Health Sciences, Construction & Engineering, Utilities, and Communications customers are able to innovate faster, increase productivity, and lower costs with a growing array of Oracle Global Business Unit solutions. AT&T, gnTel, Cordoba Corporation, Lenox and Al Nadhi are just a few of the latest customers who are benefiting from these cloud solutions.

“The worldwide cloud services market continues to display strong growth, as companies across verticals increasingly seek industry cloud applications and cloud-based solutions. Technology providers who can help companies more seamlessly share data and integrate multiple facets of the business will be best positioned to succeed,” Eric Newmark, Program Vice President, SaaS and Industry Cloud, IDC.

While each industry faces unique challenges, there are underlying complexities that transcend them all—meeting increasing customer demands, speeding data intelligence to action and enhancing agility. With a focus on providing solutions that serve as the backbone of companies’ digital transformations, Oracle Global Business Units deliver a powerful business proposition: industry specialization at a global scale and in the cloud. 

“With digital technologies now woven through every facet of society, at the heart of today’s new business environment is an even more informed and empowered customer,” said Bob Weiler, Executive Vice President, Global Business Units at Oracle. “Businesses must quickly evolve and innovate to keep pace with ever changing and growing demands, making the cloud a necessity. No one is better positioned than Oracle to help our customers navigate this new reality.”

Customers Driving Success in the Cloud

“Our focus is providing customers enduring luxury products and trend-setting brands with a fresh, engaging shopping experience. Oracle Commerce Cloud keeps us current in a rapidly changing consumer environment across online and mobile channels with over 25 percent improvement in year-over-year mobile sales,” said Ravi Kurumety, CIO, Lenox Corporation. “Consumers are inspired and empowered by a more visual, intuitive shopping experience where they can easily discover new complementary items and designs to patterns they already own.”

“Oracle’s Primavera Unifier enables Cordoba to share information fluidly between the office and the field, improve document management, and drive efficiency across business processes—all of which are critical in managing complex construction projects. A best-in-class project lifecycle solution, Primavera Unifier delivers the reliability of the Oracle cloud and many other benefits of having Oracle as a partner,” said Stephanie Lightner, Program Scheduler for Cordoba Corporation.

“At gnTel, we recognize the fast pace of technology evolution in the telecom industry. We wanted to invest in a proven solution from a company that brings together expertise in cloud, virtualization, IT, and telecommunications,” said Onno Speekenbrink, gnTel. “Oracle’s solution will position us well for future growth.”

Oracle Leading Industry Innovation in the Cloud

Oracle Global Business Units continue to unveil new cloud-based advancements, empowering users to modernize their customer experience, remove operational silos, meet new regulatory requirements and make data more accessible. Recent cloud innovations include:

  • Health Sciences: Oracle Health Sciences Clinical One Platform, a new cloud-based eClinical solution, unifies clinical development operations and information in a single environment with shared functions that saves time for everyone involved in clinical trials.
  • Utilities: Oracle Utilities Work and Asset Cloud Service is an innovation foundation for asset performance management which enables utilities to increase asset reliability by finding asset failures faster and performing predictive maintenance with integrated APM and analytics. Recently announced, Oracle Utilities Operational Device Cloud Service (ODCS) also enables utilities to further automate the management of their smart grid assets and IoT devices, at a lower total cost of ownership.
  • Construction & Engineering: Oracle Prime, Oracle Construction and Engineering’s cloud-based project success platform, features several recent enhancements. With new innovations for field management, Lean scheduling and capital planning, Oracle Prime further improves decision-making and project delivery to drive better outcomes across the project life cycle. Mobile ready and with built-in analytics, Oracle Prime connects field and office to improve collaboration and provide real-time visibility into project cost, schedules, risk and performance data.
  • Communications: Empowering innovative businesses to launch competitive pricing models in the era of digital commerce, Oracle Monetization Cloud service accelerates time to market for digital and subscription-based products and services. The solution delivers the full life cycle of customer onboarding, offer creation, robust rating and discounting, billing, customized invoicing and reporting.
  • Retail: Oracle Retail Release 16 includes cloud solutions for merchandising, planning, supply chain and omnichannel operations designed to empower retailers of all sizes to maximize margins, encourage associate engagement with consumers and free IT departments to focus on innovation projects.
  • Hospitality: Hoteliers are using Oracle Hospitality OPERA Cloud property management to simplify IT across its network of properties and create a singular guest profile to help drive loyalty and an overall better guest experience. Food and beverage operators are using Oracle Hospitality Simphony Cloud to increase revenues and operational efficiency in restaurants, bars, and stadiums through faster adoption of new guest engagement channels and centralization of menus, promotions, and reporting.
Contact Info
Judi Palmer
About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at www.oracle.com.


Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Talk to a Press Contact

Judi Palmer

  • 650-506-0266

Lojas Renner Simplifies Operations with Oracle Retail

Oracle Press Releases - Wed, 2017-08-02 10:46
Press Release
Lojas Renner Simplifies Operations with Oracle Retail Largest Brazilian Fashion Retailer Establishes a Foundation for Growth

Redwood Shores, Calif.—Aug 2, 2017

Today, Oracle announced that Brazil's largest fashion retailer, Lojas Renner has deployed Oracle Retail to transforms operations and establishes a foundation for growth. Lojas Renner operates over 470 stores under the Renner, Youcom and Camicado brands. Lojas Renner is simplifying operations and offering a shopping experience to inspire its customers even further and continue to grow.

Over time, each of the brands had selected and customized multiple solutions over the years. This disparate solution landscape made it complicated to upgrade solutions and keep pace with market trends with new innovations. In 2014, Renner embarked on an initiative to modernize technology and implement a standard solution in the primary Lojas Renner brand to drive productivity and efficiency. Renner drew on the talents and Oracle experience of the IT staff and a well-defined management structure to execute the implementation. Renner chose experienced implementation partners, including Logic, to support the transformation at scale.

“We chose modernize and simplify with Oracle Retail to reduce operating and support costs to prepare for Omnichannel in Brazil. This required Renner's processes to be revised to conform to global best practices built into the Oracle solution,” said Emerson Silveira Kuze, Chief Information Officer, Lojas Renner. “This shift in vision allowed Renner to move forward in adopting worldwide standards of efficiency and productivity in its management, while reducing implementation costs and future system upgrades.”

For the success of the project, Renner created a culture of empowerment, alignment and transparency across the entire IT team, business areas, Logic and Oracle to maintain the project schedule. Prior to embarking on the implementation in 2015, Renner laid the plans and mapped out the functional and technical specifications followed by four phases of testing. 

“We estimate that less than 5% of the Oracle Retail solution is customized (mainly due to unique issues in Brazil such as legal and tax claims) as compared to 30% of the previous platform. This new scenario will enable the company to make system upgrades more quickly,” said Emerson Silveira Kuze.

“The Brazilian market has nuances that required additional features and functionalities to compete and comply with market regulations. By partnering with multiple Brazilian retailers like Renner, we could truly understand the requirements and incorporate market requirements into our solutions and weave this local experience into our Retail Reference Library,” said Ray Carlin, Senior Vice President and General Manager, Oracle Retail.

About Oracle Retail

Oracle provides retailers with a complete, open, and integrated suite of best-of-breed business applications, cloud services, and hardware that are engineered to work together and empower commerce. Leading fashion, grocery, and specialty retailers use Oracle solutions to anticipate market changes, simplify operations and inspire authentic brand interactions. For more information, visit our website at www.oracle.com/retail.

About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at www.oracle.com.


Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

September AZORA meeting

Bobby Durrett's DBA Blog - Wed, 2017-08-02 10:30

The September AZORA meeting has a great lineup of speakers. AZORA is the Arizona Oracle User Group. Republic Services is providing us with an excellent place to meet off of the 101. I’m looking forward to it. Check it out if you are in the Phoenix area.


#meetup_oembed .mu_clearfix:after { visibility: hidden; display: block; font-size: 0; content: " "; clear: both; height: 0; }* html #meetup_oembed .mu_clearfix, *:first-child+html #meetup_oembed .mu_clearfix { zoom: 1; }#meetup_oembed { background:#eee;border:1px solid #ccc;padding:10px;-moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;margin:0; font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; font-size: 12px; }#meetup_oembed h3 { font-weight:normal; margin:0 0 10px; padding:0; line-height:26px; font-family:Georgia,Palatino,serif; font-size:24px }#meetup_oembed p { margin: 0 0 10px; padding:0; line-height:16px; }#meetup_oembed img { border:none; margin:0; padding:0; }#meetup_oembed a, #meetup_oembed a:visited, #meetup_oembed a:link { color: #1B76B3; text-decoration: none; cursor: hand; cursor: pointer; }#meetup_oembed a:hover { color: #1B76B3; text-decoration: underline; }#meetup_oembed a.mu_button { font-size:14px; -moz-border-radius:3px;-webkit-border-radius:3px;border-radius:3px;border:2px solid #A7241D;color:white!important;text-decoration:none;background-color: #CA3E47; background-image: -moz-linear-gradient(top, #ca3e47, #a8252e); background-image: -webkit-gradient(linear, left bottom, left top, color-stop(0, #a8252e), color-stop(1, #ca3e47));disvplay:inline-block;padding:5px 10px; }#meetup_oembed a.mu_button:hover { color: #fff!important; text-decoration: none; }#meetup_oembed .photo { width:50px; height:50px; overflow:hidden;background:#ccc;float:left;margin:0 5px 0 0;text-align:center;padding:1px; }#meetup_oembed .photo img { height:50px }#meetup_oembed .number { font-size:18px; }#meetup_oembed .thing { text-transform: uppercase; color: #555; }
Starting the fall with a bang! Rich Niemiec, Jerry Ward, Benoit Chaffanjon

Thursday, Sep 21, 2017, 10:00 AM

Republic Services – 2nd Floor Conference Rooms
14400 N 87th St (AZ101 & Raintree) Scottsdale, AZ

6 AZORAS Attending

Wow! We’re coming off of the summer break with a bang! We’ll have three speakers: Rich Niemiec and Jerry Ward from Viscosity, followed by Benoit Chaffanjon, Oracle VP of Enterprise Solutions and Chief Architect.We’ll also be starting earlier in the day at 10:00 am to change things up!Save the date! Thursday, September 21st, 10am-2:30pmFor the la…

Check out this Meetup →


Categories: DBA Blogs

Postgres vs. Oracle access paths II – IndexOnlyScan

Yann Neuhaus - Wed, 2017-08-02 10:00

In the previous post I’ve explained a sequential scan by accident: my query needed only one column which was indexed, and I expected to read the index rather than the table. And I had to hint the Oracle example to get the same because the Oracle optimizer chooses the index scan over the table scan in that case. Here is where I learned a big difference between Postgres and Oracle. They both use MVCC to query without locking, but Postgres MVCC is for table rows (tuples) only whereas Oracle MVCC is for all blocks – tables and indexes.

So this second post is about Index Only Scan and the second constant you find in the documentation for the query planner:
random_page_cost (floating point)
Sets the planner’s estimate of the cost of a non-sequentially-fetched disk page. The default is 4.0.

I am here in the situation after the previous post: created table and index, have run a query which did a sequential scan on the table:

explain (analyze,verbose,costs,buffers) select sum(n) from demo1 ;
Aggregate (cost=1554.00..1554.01 rows=1 width=8) (actual time=17.430..17.430 rows=1 loops=1)
Output: sum(n)
Buffers: shared hit=1429
-> Seq Scan on public.demo1 (cost=0.00..1529.00 rows=10000 width=4) (actual time=0.031..13.011 rows=10000 loops=1)
Output: n, a, x
Buffers: shared hit=1429
Planning time: 1.791 ms
Execution time: 17.505 ms

Index Only Scan

I want to understand why the query planner did not choose an access to the index only. This is where hints are useful: force a plan that is not chosen by the optimizer in order to check if this plan is possible, and then check its cost:

/*+ IndexOnlyScan(demo1) */
explain (analyze,verbose,costs,buffers) select sum(n) from demo1 ;
Aggregate (cost=1727.29..1727.30 rows=1 width=8) (actual time=5.424..5.425 rows=1 loops=1)
Output: sum(n)
Buffers: shared hit=1429 read=29
-> Index Only Scan using demo1_n on public.demo1 (cost=0.29..1702.29 rows=10000 width=4) (actual time=0.177..4.613 rows=10000 loops=1)
Output: n
Heap Fetches: 10000
Buffers: shared hit=1429 read=29
Planning time: 0.390 ms
Execution time: 5.448 ms

From there you see that an Index Only Scan is possible but more expensive. The estimated cost is higher than the Seq Scan (cost=0.29..1702.29 instead of cost=0.00..1529.00). And the execution statistics shows that I’ve read the 1429 table pages in addition to the 29 pages of the index.

From the hit/read statistics we can note that the create table has left all the table pages in the buffer cache, but this is not the case for the create index. But that’s another story. My concern is why and index only access goes to read all table blocks in addition to the index ones, which brings the cost to 1727.30-1554.01=173.29 higher than the sequential scan.

The clue is in this line showing that all my rows were fetched from heap page, which is the table: Heap Fetches: 10000

Tuple visibility

In ACID databases, a modification must not be visible by others until the transaction completion (commit). There are two ways to achieve that. The first way is to read the latest version of data: lock in share mode what you read, so that no concurrent update can happen. The other solution is to query a previous version of data (MVCC – Multi Version Concurrency Control) where uncommitted changes are not visible. Both Oracle and Postgres use MVCC which is great because you can have transactions and queries on the same database. But they do the versioning at a different level.

Oracle MVCC is physical, at block level. Then everything is versioned: tables as well as index, with their transaction information (ITL) which, with the help of the transaction table, give all information about visibility: committed or not, and with the commit SCN. With this architecture, a modified block can be written to disk even with uncommitted changes and there is no need to re-visit it later once the transaction is committed.

Postgres MVCC is logical at row (‘tuple’) level: new version is a new row, and committed changes set the visibility of the row. The table row is versioned but not the index entry. If you access by index, you still need to go to the table to see if the row is visible to you. This is why I had heap fetches here and the table blocks were read.

This explains that the cost of Index Only Scan is high here. In addition to about 30 index blocks to read, I’ve read about 1429 table blocks. But that can be worse. For each index entry, and I have 10000 of them, we need to go to the table row, which is exactly what the 10000 heap fetches are. But I’m lucky because I have a very good clustering factor: I have created the table with increasing values for the column N (generated by generate_series). With a bad clustering factor (physical storage of rows in the table not correlated with the order of index) you would see up to 10000 additional shared hits. Thankfully, the query planner estimates this and has switched to table scan which is cheaper in this case.

Vacuum and Visibility Map

Always going to the table rows to see if they are committed would always be more expensive than a table scan. The Postgres vacuum process maintains a Visibility Map as a bitmap of pages that have been vacuumed and have no more tuples to vacuum. This means that all rows in those pages are visible to all transactions. When there is an update on the page, the flag is unset, and remains unset until the modification is committed and the vacuum runs on it. This visibility flag is used by the Index Only Scan to know if it is needed to get to the page.

Let’s run the vacuum and try again the same query:

vacuum demo1;
explain (analyze,verbose,costs,buffers) select sum(n) from demo1 ;
Aggregate (cost=295.29..295.30 rows=1 width=8) (actual time=2.192..2.193 rows=1 loops=1)
Output: sum(n)
Buffers: shared hit=30
-> Index Only Scan using demo1_n on public.demo1 (cost=0.29..270.29 rows=10000 width=4) (actual time=0.150..1.277 rows=10000 loops=1)
Output: n
Heap Fetches: 0
Buffers: shared hit=30
Planning time: 0.450 ms
Execution time: 2.213 ms

Here, without any hint, the query planner has chosen the Index Only Scan which is now less expensive than a Seq Scan: cost=0.29..270.29

Cost of Index Only Scan

There is an initial cost of 0.29 is calculated from cpu_operator_cost which defaults 0.0025 which means that about 0.29/0.0025=116 operations were charged here. This cost is minimal and I don’t go into details.
Then, to get rows we have to

  • read 30 blocks from the index. Those seem to be random scan (with random_page_cost=4) and then the cost for all rows is 4*30=120
  • process the index entries (with cpu_index_tuple_cost=0.005) and then the cost for all 10000 rows is 0.005*10000=50
  • process the result rows (with cpu_tuple_cost=0.01) and then the cost for all 10000 rows is 0.01*10000=100

This brings the cost to the total of 270.29

For the above operation, the SUM(N) this is exactly the same as in the previous post on Seq Scan: cost=25 (cpu_operator_cost=0.0025 for 10000 rows) and is this initial cost because the sum is now only when all rows are processed, and an additional 0.01 for the result row.


In the previous post I used the FULL() hint to compare Oracle Full Table Scan to Postgres Seq Scan, but by default, Oracle chose an index only access because the index covers all the rows and columns we need.

All columns that we need:

In the previous post we have seen the column projection (from the +projeciton format of dbms_xplan):

Column Projection Information (identified by operation id):
1 - (#keys=0) SUM("N")[22] 2 - (rowset=256) "N"[NUMBER,22]

I need only the column N from the table DEMO1, and this column is in the index DEMO1_N

All rows that we need:

In Oracle an index does not have an entry for every row but only for rows where at least one of the indexed columns is not null. Here because we have no where clause predicate on N, and because we have not declared the column N as NOT NULL, the access by index may not return all rows. However, the SUM() function does not need to know about the null values, because they don’t change the sum and then the optimizer can safely choose to do an index only access.

Here is the query without hints:

SQL_ID 6z194712fvcfu, child number 0
select /*+ */ sum(n) from demo1
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
| 0 | SELECT STATEMENT | | 1 | | 7 (100)| 1 |00:00:00.01 | 26 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 26 |
| 2 | INDEX FAST FULL SCAN| DEMO1_N | 1 | 10000 | 7 (0)| 10000 |00:00:00.01 | 26 |
Column Projection Information (identified by operation id):
1 - (#keys=0) SUM("N")[22] 2 - "N"[NUMBER,22]

This plan looks very similar to the Postgres one after the vacuum: 51 buffers which is approximately the number of blocks in my index here. However, Oracle does not have the ‘vacuum’ requirement because the MVCC applies to the index and Oracle does not need to go to the table to undo the uncommitted changes. But there is something else here. If you remember the previous post, the Oracle cost=1 is equivalent to the cost of a random read (single block) and the cost of reading one block through a larger I/O (multiblock read) is, with default statistics, about 0.278 times cheaper. Here, 7/26= 0.2692 which proves that the cost is based on multiblock reads. Oracle can read indexes with INDEX FAST FULL SCAN in the same way it reads table with FULL TABLE SCAN: with larger I/O. We don’t need any ordering of rows here, because we just do the sum, and then we don’t need to follow the chain of leaf blocks, scattered within the index segment. Just read all blocks as they come, with fast I/O.

Index Fast Full Scan is possible in Oracle because MVCC is at block level for indexes as well as tables. You can just read the blocks as of the point in time of the query, without being concerned by concurrent operations that update the index entries or split the blocks. Postgres Index Only Scan is limited because MVCC is on tables only, and then must scan the index in the order of leaves, and must read the visibility map and maybe the table pages.

In Oracle, an index can be used to partition vertically a table, asa redundant storage of a few columns in order to avoid full table scans on large rows, allowing queries to avoid completely to read the table when the index covers all required rows and columns. We will see more about the ‘all rows’ requirement in the next post.


Cet article Postgres vs. Oracle access paths II – IndexOnlyScan est apparu en premier sur Blog dbi services.

Oracle 12c Release 2 Summer Event Live Stream

Gerger Consulting - Wed, 2017-08-02 08:14
Join our live stream on August 8th and watch two ACE Directors, Richard Niemiec and Nitin Vengurlekar talk about their favorite new features of Oracle 12c Release 2, Oracle DBaaS, RAC, ASM, partitioning and encryption. Register at this link.

Below is the agenda of the live stream. All times are in PST:

  • 7:30 - 8:30 Understanding 12cR2 Grid Infrastructure, RAC, and ASM/ACFS.
    In this session, Nitin Vengurlekar will take a deep-dive into the new features of 12cR2 Grid Infrastructure, RAC, and ASM/ACFS.
  • 8:30 - 9:30 Oracle 12cR2 Multi-Tenant Options.
    Learn to leverage all the latest features of Oracle 12cR2 that enable the journey to a private, hybrid, or public cloud environment with Nitin Vengurlekar.
  • 10:00 - 11:00 Taking advantage of Oracle 12cR2.
    In this session, Rich Niemiec will tackle Partitioning, encryption, object names, and Monitoring with OEM/APM.
  • 11:00 - 12:00 Oracle Cloud Structure on 12c Release 2.
    Learn how to set up your first Cloud database and DBaaS with Rich Niemiec.
  • 12:00 - 12:45 Basics of Bare Metal Cloud Service Offering.
    Nitin Vengurlekar discusses the Basics of the BMCS offering and features.
Categories: Development

Transparent Data Encryption Architecture: Quickly and Easily

We have covered in the past a lot of information on database security via posts and partner webcasts such as Partner Webcast - Oracle Database Security Inside-Out. Moving to cloud, data...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Oracle Delivers Next-Generation Cloud Applications

Oracle Press Releases - Wed, 2017-08-02 07:00
Press Release
Oracle Delivers Next-Generation Cloud Applications Innovations across Oracle Cloud Applications extend industry’s broadest, deepest, and fastest growing suite of cloud applications

Redwood Shores, Calif.—Aug 2, 2017

To help organizations around the world grow faster, differentiate from competitors, and better serve their customers, Oracle today announced significant new capabilities and enhancements to Oracle Cloud Applications. With the introduction of Oracle Cloud Applications Release 13, Oracle is further extending the industry’s broadest, deepest, and fastest growing suite of cloud applications. Innovations in the new release enhance the user experience and empower business users across the organization including customer experience, finance, HR, and supply chain professionals.

“We are committed to helping organizations of all sizes transform critical business functions to drive their growth and stay competitive,” said Steve Miranda, executive vice president of applications development, Oracle. “With the latest release of Oracle Cloud Applications, we are introducing hundreds of new innovations. The latest updates include major enhancements to our supply chain management suite that will help customers create intelligent, connected, and customer-centric supply chains. In addition, we are introducing a brand new solution that enriches the customer experience by bridging the gap between sales and customer service. The new release also includes further advancements to the user experience and customer-driven changes for human resources and finance.”

Oracle Cloud Applications provide a complete and fully integrated suite of applications that allow organizations to increase business agility and reduce costs. The latest release includes new capabilities and enhancements across Oracle Supply Chain Management (SCM) Cloud, Oracle Customer Experience (CX) Cloud Suite, Oracle Enterprise Resource Planning (ERP) Cloud and Oracle Human Capital Management (HCM) Cloud. In addition, Oracle has enhanced the user experience across Oracle Cloud Applications to help customers personalize their experience and further improve productivity, insight, and collaboration.

Oracle SCM Cloud

Oracle SCM Cloud delivers the end-to-end visibility, insights, and capabilities that organizations need to create intelligent supply chains. Oracle SCM Cloud Release 13 extends the most comprehensive SCM suite in the cloud with the introduction of more than 200 major features and six new products that cover Sales and Operation Planning, Demand Management, Supply Planning, Collaboration, Quality Management and Maintenance. The new innovations help organizations transform their operating models to meet rapidly changing business demands by evolving from traditional supply chain systems to connected, comprehensive, agile, and customer-oriented supply chain management capabilities.

Oracle CX Cloud Suite

Oracle CX Cloud Suite empowers organizations to take a smarter approach to customer experience management and business transformation initiatives by providing a trusted business platform that connects customer data, experiences, and outcomes. Oracle CX Cloud Suite Release 13 introduces new innovations to Oracle Sales Cloud, which include enhanced mobile and data visualization capabilities, as well as a range of new capabilities that increase sales rep productivity. In addition, Oracle has extended Oracle CX Cloud Suite with the introduction of Oracle Engagement Cloud. The new solution combines sales and service capabilities to enable organizations to increase customer satisfaction, loyalty, and up-sell opportunities.

Oracle ERP Cloud

Oracle ERP Cloud is the industry’s leading and most complete, modern, and secure financial platform delivered seamlessly through the Oracle Cloud. Oracle ERP Cloud helps organizations drive innovation and business transformation by increasing business agility, lowering costs, and reducing IT complexity. Oracle ERP Cloud Release 13 builds upon the industry’s broadest and most integrated public cloud. Extended depth and breadth across Financials, Procurement, and Project Portfolio Management (PPM) help organizations accelerate the pace of innovation via deeper domain functionality including Dynamic Discounting and Multi-Funding. In addition, industry coverage for higher education, financial services, and manufacturing, as well as expanded country localizations for India and Brazil, enable organizations of all sizes, and from different industries and geographies, to quickly and easily take advantage of the new release.

Oracle HCM Cloud

Oracle HCM Cloud provides organizations with modern HR technologies that enable collaboration, optimize talent management, provide complete workforce insights, increase operational efficiency, and make it easy for everyone to connect on any device. Oracle HCM Cloud Release 13 extends Oracle’s commitment to customer success with 80 percent of enhancements being customer driven. Release 13 enhances Oracle’s complete, end-to-end solution for all HCM processes by introducing expanded user experience personalization and branding and additional Tier 1 localization support. It also includes improved capabilities to support the needs of customers with unionized workforces, such as retail and healthcare with flexible work models.

Contact Info
Simon Jones
PR for Oracle
About Oracle

The Oracle Cloud offers complete SaaS application suites for ERP, HCM and CX, plus best-in-class database Platform as a Service (PaaS) and Infrastructure as a Service (IaaS) from data centers throughout the Americas, Europe and Asia. For more information about Oracle (NYSE:ORCL), please visit us at www.oracle.com


Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Safe Harbor

The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle Corporation. 

Talk to a Press Contact

Simon Jones

  • +1.415.856.5155

Video: Transport Layer Security in Oracle ICS and MCS

OTN TechBlog - Wed, 2017-08-02 07:00

How secure is your connection to your Oracle Mobile Cloud or Integration Cloud service? In order to answer that question, Oracle ACE Associate Maarten Smeets, senior integration consultant with AMIS Services, suggests in his latest 2 Minute Tech Tip that you look at the various layers in the Open Systems Interconnection (OSI) model. Maarten explains that the data layer and segment or transport layer are particularly important. "The hardware layers are taken care of by Oracle since ICS and MCS are PaaS solutions. For security in the data layer you often see the use of Secure Token Services. Authentication takes place by a client and a token is returned. This token can be used for subsequent authentications and can be validated against the secure token service. Examples of this are SAML, JWT, and OAuth." Watch Maarten's tip for more detail.

Additional Resources

Queue_to_Queue Propagation does not Propagate Messages but no errors are reported

Tom Kyte - Wed, 2017-08-02 04:26
We are trying to set up an AQ Schedule Propagation between two JMS (RIB version 13.1.8 to RIB version 16.0.0). We followed all documentation steps to set this up, but even with the AQ JOB created and running, the message is not being dequeued from th...
Categories: DBA Blogs

Same query from multipule sessions, but dont see Read By Multipule Session

Tom Kyte - Wed, 2017-08-02 04:26
Hi, I was going through the below link, and doing some testing of my own. http://oracleinaction.com/simulate-buff-busy/ Focusing only on the below. PROCEDURE RUNSELECT as cursor dummy is select * from t; c1 t_refcur; rec dummy...
Categories: DBA Blogs

Oracle Text - synonym of a word !

Tom Kyte - Wed, 2017-08-02 04:26
Hi All, I have questions about Oracle Text 12c. And I would like to have your supports on this. 1. Synonym of a word For example, I have a word "REVEAL" and can I get all the synonyms of the REVEAL, such as: show, display, exhibit, disclose, u...
Categories: DBA Blogs

Truncate Statement running for hours in

Tom Kyte - Wed, 2017-08-02 04:26
Team: As requested at this link (https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:283015939157#9536017900346127115 ) posted this as a new question. Very recently in our database, we identified that few "Truncate Table" statem...
Categories: DBA Blogs


Tom Kyte - Wed, 2017-08-02 04:26
Hi Masters, I have below questions with oracle Model clause: 1. Was the name Spreadsheet initially ? When to use Model clause, does it worth learning for oracle ebs developers ? or is it totally warehousing thing ? 2. Why so many people don't us...
Categories: DBA Blogs

Impdp import error, ORA-39001

Tom Kyte - Wed, 2017-08-02 04:26
Hello, Before posting I browsed the questions and found a similar issue here: https://asktom.oracle.com/pls/apex/f?p=100:11:4134964994658::::P11_QUESTION_ID:9526433800346760378 However, only point (2) of the answer given to the OP is helpful, b...
Categories: DBA Blogs


Subscribe to Oracle FAQ aggregator